Formulas not updating when adding rows

honkin

Board Regular
Joined
Mar 20, 2012
Messages
230
Office Version
  1. 2016
Platform
  1. MacOS
I have a sheet with around 5,000 rows in it and a series of calculation boxes below the data.

Screen Shot 2564-04-27 at 11.26.32.png


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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

honkin

Board Regular
Joined
Mar 20, 2012
Messages
230
Office Version
  1. 2016
Platform
  1. MacOS
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

Board Regular
Joined
Mar 20, 2012
Messages
230
Office Version
  1. 2016
Platform
  1. MacOS
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
 

Attachments

  • Screen Shot 2564-05-02 at 13.56.03.png
    Screen Shot 2564-05-02 at 13.56.03.png
    95.6 KB · Views: 2

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,244
Office Version
  1. 2010

ADVERTISEMENT

You're welcome
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
230
Office Version
  1. 2016
Platform
  1. MacOS
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

Board Regular
Joined
Mar 20, 2012
Messages
230
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

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

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 20, 2012
Messages
230
Office Version
  1. 2016
Platform
  1. MacOS
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

Board Regular
Joined
Mar 20, 2012
Messages
230
Office Version
  1. 2016
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,704
Messages
5,654,829
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top