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:

SNAGHTML958c98

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?

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

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

  3. I prefer the following to avoid the volatility of OFFSET.

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

    Regards

  4. 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!)

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

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *