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
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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
I'd definitely toy with the idea, because in your formula, everything ends in 1048576... or in other words the bottom of that current range of data in that column AD correct? So it should still work to just referencing the entire column instead in every part of the formula as AD:AD. If not, there's usually other ways to calculate what you're trying to do that would work with open-ended column ranges. Can you post some trial data so I can test your formula? It's hard to visualize why you're using offset in the SUBTOTAL with minus MIN etc... thanks!

Also, generally, if the headers are texts, then they usually don't impact a math-based calculation over an open range, so you still be able to get away with not starting the range on Row 3, and keep AD:AD.

You're not looking for a VBA solution correct? just a formula one?

(Sorry for just now noticing your previous comment, "The reason SUBTOTAL was used was so I could have it work on only visible cells"
You have hidden rows in your data, why may I ask? Can you "flag" those rows you want not caluclated somehow, and use that flag as a condition criteria in a SUMIFS or COUNTIFS to not have it calculated in your tables?) This is interesting...
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

honkin

Board Regular
Joined
Mar 20, 2012
Messages
230
Office Version
  1. 2016
Platform
  1. MacOS
I'd definitely toy with the idea, because in your formula, everything ends in 1048576... or in other words the bottom of that current range of data in that column AD correct? So it should still work to just referencing the entire column instead in every part of the formula as AD:AD. If not, there's usually other ways to calculate what you're trying to do that would work with open-ended column ranges. Can you post some trial data so I can test your formula? It's hard to visualize why you're using offset in the SUBTOTAL with minus MIN etc... thanks!

Also, generally, if the headers are texts, then they usually don't impact a math-based calculation over an open range, so you still be able to get away with not starting the range on Row 3, and keep AD:AD.

You're not looking for a VBA solution correct? just a formula one?

(Sorry for just now noticing your previous comment, "The reason SUBTOTAL was used was so I could have it work on only visible cells"
You have hidden rows in your data, why may I ask? Can you "flag" those rows you want not calculated somehow, and use that flag as a condition criteria in a SUMIFS or COUNTIFS to not have it calculated in your tables?) This is interesting...
cheers @Oddball2020

There are not any hidden rows when the data is not autofiltered, but being records of past results, when trying to develop profitable systems, many filters are employed for testing, so that is when cells become hidden. I need the formulas to work regardless of what is visible, which is how it functions now. The issue has always been adding a new month of data, as the previous month ends and results are added to the end of the existing data, but before the final statistics boxes, which are displayed in the original post.

Was definitely not looking for a VBA solution, as the formula method seems to act appropriately.

So, in this formula =SUMPRODUCT(SUBTOTAL(103,OFFSET(AE:AE,ROW(AE:AE)-MIN(ROW(AE:AE)),,1))*(AE:AE>=0)), the answer comes back as 715, but when I actually target the range correctly using AE2:AE:1803 or even going for AE2:AE1048576, the answer is 1714; which is correct. Similarly with other formulas, where the answer will be out by 1.

Depending on the sheet, there will be either 1 single header row, or possibly 2 header rows. There are not any figures in the cells addressed in those headers, just text.

In the end, using the approach of nominating the range seems to work fine. Depending on the row, using the first data cell, ie AE2 and for a sheet which will be updated monthly, using the last possible cell in the column ie AE1048576, seems to do the job fine. If the sheet will always be static, then just the last cell of data in the relevant column

If you think it is too cumbersome, happy to look at an alternative, but it seems to work as planned right now

cheers
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
cheers @Oddball2020

There are not any hidden rows when the data is not autofiltered, but being records of past results, when trying to develop profitable systems, many filters are employed for testing, so that is when cells become hidden. I need the formulas to work regardless of what is visible, which is how it functions now. The issue has always been adding a new month of data, as the previous month ends and results are added to the end of the existing data, but before the final statistics boxes, which are displayed in the original post.

Was definitely not looking for a VBA solution, as the formula method seems to act appropriately.

So, in this formula =SUMPRODUCT(SUBTOTAL(103,OFFSET(AE:AE,ROW(AE:AE)-MIN(ROW(AE:AE)),,1))*(AE:AE>=0)), the answer comes back as 715, but when I actually target the range correctly using AE2:AE:1803 or even going for AE2:AE1048576, the answer is 1714; which is correct. Similarly with other formulas, where the answer will be out by 1.

Depending on the sheet, there will be either 1 single header row, or possibly 2 header rows. There are not any figures in the cells addressed in those headers, just text.

In the end, using the approach of nominating the range seems to work fine. Depending on the row, using the first data cell, ie AE2 and for a sheet which will be updated monthly, using the last possible cell in the column ie AE1048576, seems to do the job fine. If the sheet will always be static, then just the last cell of data in the relevant column

If you think it is too cumbersome, happy to look at an alternative, but it seems to work as planned right now

cheers
If it works for you and makes sense, go with it! All the best!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,708
Messages
5,654,856
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