Posts by 3butts

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • is it possible to use an inputted cell value as the number of the starting row in a range?

    Hi,

    I hope you are all well. I have a number of Countifs in a spreadsheet to count how many people on a waiting list will have been waiting over 12 weeks each month over many months, with each column being a month, and each row the number of weeks a person has been waiting, in descending order e.g. Countif(A2:A5000,">12"). I need to look at the impact of increasing activity. So for example if we saw an extra 50 people the Countif would start at row 52 the following month/column instead of row 2. I need to look at various scenarios which would be a huge manual task to keep amending all the Countifs. I wondered of it is it possible to vary the "2" in A2 using a value inputted into a cell, rather than changing it manually in each Countif? e.g. Countif(A plus 'value in cell A1':A5000,">12").

    Many thanks for your help,

    Pete

  • VBA to find a cell in the last row of a table and drag it down to the next row

    Hi. In a worksheet ("SPCchartsdata") I have about a hundred tables that are used to produce charts. At the moment I am manually dragging the "Month" value from the last row in each table down to the row below in order to create a new row for the latest month. The dragging makes all the other columns automatically update. The first table "Table1" covers columns A to H. using Table1 as an example, is there a way you can show me how to automate the creation of the new row with a Macro please? Column B contains the month values. Many thanks for your help, Pete.