OFFSET or Named Range – Which would you use?

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:

=SUBTOTAL(9,L62:OFFSET(L66,-1,0))

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…

5 thoughts on “OFFSET or Named Range – Which would you use?”

Sara on September 9, 2013 at 5:33 pm said:

Both. Create a named range using an offset formula. As long as the inserted rows are contiguous to the other rows, the formula in the range will pick them up.
Rudi on September 10, 2013 at 1:35 am said:

I'd prefer the name range method, firstly as it's more "Tamper proof" to the basic user. Secondly, the formula in the name range is MUCH more simpler than the OFFSET() function, esp. if one must guide a user through a support call. Thirdly, OFFSET() has a reputation of slowing down the sheet/WB if overused. The name range method will avoid this completely.

Thanks for your great blog and Excel advice.
Cheers 🙂
Elias on September 10, 2013 at 4:40 am said:

I prefer the following to avoid the volatility of OFFSET.

=SUBTOTAL(9,L62:INDEX(L:L,ROW()-1))

Regards
Ken Puls on September 10, 2013 at 7:52 am said:

Thanks everyone!

Sara, I'm curious why couple the OFFSET function with the Named Range. It seems like duplication with the issue of slowing down the workbook that Rudi mentioned.

Elias, clever! I haven't seen that approach used before. 🙂

For my own, like Rudi, I prefer the named range method. I find it makes the formulas a bit more readable and supportable than the other methods. (I think either OFFSET or INDEX would scare a lot of users away from the formula -- which may not be a bad thing if you don't want them to tamper with it!)
Patrick on September 10, 2013 at 2:10 pm said:

I'm with Elias. I use that sort of INDEX construction frequently, to the point where I almost never use OFFSET.

If I were handing this off to someone else I would be tempted to use the named range if for no other reason than to make the formula "self-documenting".