Formulas not updating when adding rows

[honkin]

I have a sheet with around 5,000 rows in it and a series of calculation boxes below the data.

As the sheet is a live results record, new results are added monthly, once the full month's results are through.

My question is, how do you add multiple rows to a sheet, around 150, and have the calculations in the image above update to the new row total? Each calculation currently ends at 4,460, but with 150 or so new rows expected once April is completed, I want to lighten the workload, as normally I add the rows and have to update each and every cell in those calculation boxes.

If I was working within the same sheet, there is always an option to Insert Copied Cells, but I do not see that as an option when copying from a different workbook.

As an example, the # of Selections cell uses the following to give me the visible number of selections =SUBTOTAL(103,AC3:AC6487). Each cell has something similar to give the desired results. I tried highlighting 150 rows from where I want and the rows were inserted perfectly, but instead of the formula updating, it still showed =SUBTOTAL(103,AC3:AC6487) instead of =SUBTOTAL(103,AC3:AC6637)

cheers

 

[arthurbr]

Why not use Excel Tables which will adapt automatically ?

 

[honkin]

Why not use Excel Tables which will adapt automatically ?

cheers and thanks for your reply @arthurbr

I had not wanted to go that route initially as there was nothing of benefit to be gained except the automatic updating of the rows. None of the main reasons for using tables actually applied, but if it is the only way to have the data update, then I may be forced to go that route

Thanks again

 

[honkin]

Why not use Excel Tables which will adapt automatically ?

Hi again @arthurbr

I had a bit of a play around and this is what I came up with



I did it as 4 separate tables. I can live with the dropdown arrow, even though I have no need for it at all. I formatted the text it put there as the same colour as the cell, so at least it hasn't made it look too bad.

If that at least updates as rows are added, then it will be spot on

Thanks again

 

[arthurbr]

You're welcome

 

[honkin]

You're welcome

Just an update on this. Nothing changes when additional rows are added between those data boxes and the last row of actual data. Any formulas in those data boxes remain exactly the same. Thanks so much for the effort, but it appears to do nothing

cheers

 

[honkin]

Hi again @arthurbr

There something slightly odd in the behaviour exhibited when new rows are added, now that the data boxes are tables.

So some of the formulas actually do update. These types of ones work - =F182540/(F182538*100) This is a formula addressing other cells in the table and it updates fine, as do all the ones which only reference similar cells

Any of the formulas like this one, =SUBTOTAL(103,AC3:AC178055) or , which references a range, do not update, although ones with a range which also have a single cell within the table referenced, like this one, =SUMPRODUCT(SUBTOTAL(103,OFFSET(AD3:AD178055,ROW(AD3:AD178055)-MIN(ROW(AD3:AD178055)),,1))*(AD3:AD178055>0))/F182538, have that single cell reference changed, but the rest of the code with ranges remains unchanged

In your experience, is this normal for Excel? I was most definitely hoping for all ranges to changing, adding on the number of rows added, but this fails to occur. If that is normal behaviour, I guess I have found a limitation of the use of tables

Any thoughts?

Thanks again for your help

 

[Oddball2020]

I have a sheet with around 5,000 rows in it and a series of calculation boxes below the data.

View attachment 37569

As the sheet is a live results record, new results are added monthly, once the full month's results are through.

My question is, how do you add multiple rows to a sheet, around 150, and have the calculations in the image above update to the new row total? Each calculation currently ends at 4,460, but with 150 or so new rows expected once April is completed, I want to lighten the workload, as normally I add the rows and have to update each and every cell in those calculation boxes.

If I was working within the same sheet, there is always an option to Insert Copied Cells, but I do not see that as an option when copying from a different workbook.

As an example, the # of Selections cell uses the following to give me the visible number of selections =SUBTOTAL(103,AC3:AC6487). Each cell has something similar to give the desired results. I tried highlighting 150 rows from where I want and the rows were inserted perfectly, but instead of the formula updating, it still showed =SUBTOTAL(103,AC3:AC6487) instead of =SUBTOTAL(103,AC3:AC6637)

cheers

Using SUBTOTAL 103 is the same thing as COUNTA evidently.

Change the formula to reflect across the entire length of the column... don't bound it with an end range.

So your formula is instead =COUNTA(AC:AC)

Then ensure nothing on your source data sheet has anything else below it (it's not sectioned for instance).... then as you keep on added data to that list, the formula stays the same and the result is recalculated automatically as the formula is looking down the entire column, not a bound range.

I do this all the time with my dashboards... uses COUNTIFs and SUMIFS etc down the entire columns. Never have to adjust my dashboard formula as I add more data to the source sheet list.

 

[honkin]

Using SUBTOTAL 103 is the same thing as COUNTA evidently.

Change the formula to reflect across the entire length of the column... don't bound it with an end range.

So your formula is instead =COUNTA(AC:AC)

Then ensure nothing on your source data sheet has anything else below it (it's not sectioned for instance).... then as you keep on added data to that list, the formula stays the same and the result is recalculated automatically as the formula is looking down the entire column, not a bound range.

I do this all the time with my dashboards... uses COUNTIFs and SUMIFS etc down the entire columns. Never have to adjust my dashboard formula as I add more data to the source sheet list.

Cheers @Oddball2020 and thanks for your reply

I think you may be right for that smaller formula, but am wondering about longer ones like this =SUMPRODUCT(SUBTOTAL(103,OFFSET(AD3:AD178055,ROW(AD3:AD178055)-MIN(ROW(AD3:AD178055)),,1))*(AD3:AD178055>0))/F182538 That one and others which are similar will be much more difficult to try and workaround, or are you guessing simply changing the range to things like AD:AD instead of having a numbered range may solve the issue?

The reason SUBTOTAL was used was so I could have it work on only visible cells, so that is the important part of this'
cheers

 

[honkin]

On doing a little more digging, I found that taking some of @Oddball2020's advice may help resolve this

Some of the calculations which include a <0 or >0 can be out by maybe 1, possibly to a blank cell in row 1 or 2, so instead of simply referencing the entire column, as the first 2 rows are headers, I have the calculations to reference the columns in this way AD3:AD$1048576. The absolute reference on the row just helps for when rows are added.

As an example, this seems to work, =SUMPRODUCT(SUBTOTAL(103,OFFSET(AD3:AD$1048576,ROW(AD3:AD$1048576)-MIN(ROW(AD3:AD$1048576)),,1))*(AD3:AD$1048576<0)) but is that way of referencing the column cumbersome or acceptable? I basically want any of the columns which have calculations to start from row 3 and include the rest of the column. To date, this is how the calculations look, but if there is a more efficient way to do it, I am open to suggestions

cheers