I’m working on a spreadsheet where users will be able (required) to insert new rows at a later date. When they do so, it’s critical that the section subtotals always… well… subtotal correctly.
The challenge, of course, is that you can’t rely on newly inserted rows being picked up by the subtotal formulas, so someone needs to check them. At least, you can, but it takes more than just a SUM or SUBTOTAL formula to get it done.
I reached back to the method using a named range that I describe in the “Always Refer to the Cell Above” Excelguru KB Entry, resulting in a formula that looks like this:
Of course, I don’t actually need to use the named range to do this. I could make it work by using the OFFSET function in L66 as follows:
Either will work just fine, and will not be tripped up by a user inserting a new row within my boundary, so I should never (okay, never say never) run into an issue with this particular problem.
I’m curious which method you would use? Named Range or OFFSET, and why…