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

 

[Oddball2020]

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...

 

[honkin]

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]

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!