Best way to define a self-expanding range?

[JenniferMurphy]

I have a table of data to which I periodically add rows and/or columns. I have several expressions that refer to the entire range, such as:

=Sum(C6:F11)
=MyFn(C6:F11)

If I add a row in the middle, it still works. The range in the calls gets expanded. But if I add one before the top row or after the bottom row, the range is not expanded and the new row is not included in the calculations.

My next approach was to name the cells just above the upper left corner and just below the lower right corner. Let's say I gave C5 the name "RangeBeg" and F12 the name "RangeEnd". Now I can use this call:

=Sum(offset(RangeBeg,1,0):offset(RangeEnd,-1,0))
=MyFn(offset(RangeBeg,1,0):offset(RangeEnd,-1,0))

and I can add rows anywhere and the range will be correct.

Another approach that simplifies the expressions is to name the entire range. If I assign the name RangeAll to C6:F11, then my calls can look like this:

=Sum(RangeAll)
=MyFn(RangeAll)

This is elegant, but it has the same problem as the first approach. Rows that are added at the ends will not expand the range.

What if I create a new named range that includes a one-cell border all the way around the body of the table (RangeAllPlus = B5:G13)? Then I can use the last 2 parameters to the Offset function to get expressions that dynamically extend no matter a row or column is added:

=SUM(OFFSET(RangeAllPlus,1,1,ROWS(RangeAllPlus)-2,COLUMNS(RangeAllPlus)-2))

This works, but it is ugly. Is there a better, or at least prettier, way?

 

[Lukasier]

The easiest and most "elegant" way is to format your range to table, name it however you want it and then you can easly add rows/columns and they will attach to table. Then this approach:
=Sum(RangeAll)
=MyFn(RangeAll)
will work

 

[Tetra201]

Why not define RangeAll directly in the Name Manager as:

=OFFSET(RangeAllPlus,1,1,ROWS(RangeAllPlus)-2,COLUMNS(RangeAllPlus)-2)

or non-volatile

=INDEX(RangeAllPlus,2,2):INDEX(RangeAllPlus,ROWS(RangeAllPlus)-1,COLUMNS(RangeAllPlus)-1)

 

[JenniferMurphy]

The easiest and most "elegant" way is to format your range to table, name it however you want it and then you can easly add rows/columns and they will attach to table. Then this approach:
=Sum(RangeAll)
=MyFn(RangeAll)
will work

I didn't know about this feature. I'll have to do a little studying.

I also notice that there is a Group option in the Outline section of the Data tab. Is this something completely different and not applicable to this problem?

Thanks

 

[JenniferMurphy]

Why not define RangeAll directly in the Name Manager as:

=OFFSET(RangeAllPlus,1,1,ROWS(RangeAllPlus)-2,COLUMNS(RangeAllPlus)-2)

or non-volatile

=INDEX(RangeAllPlus,2,2):INDEX(RangeAllPlus,ROWS(RangeAllPlus)-1,COLUMNS(RangeAllPlus)-1)

I didn't know I could put expressions in the Name Manager. That opens up a whole lot of opportunities. Thanks.

Do you prefer this to Lukasier's suggestion?

 

[Tetra201]

... Do you prefer this to Lukasier's suggestion?

No. This is just of the multiple ways of reaching the goal.

Tables have nice functionality. They auto-expand when you enter data beneath the last row or next to the last column. They can be easily expanded or shrunk by moving the handle in the lower right corner. Structured references (the way of addressing cells/rows/columns in a table) can get tricky, though. Just try -- you might like using tables.

The Group option is something completely different. In brief, it's a convenient way of collapsing and expanding groups of rows and columns.

 

[JenniferMurphy]

No. This is just of the multiple ways of reaching the goal.

Tables have nice functionality. They auto-expand when you enter data beneath the last row or next to the last column. They can be easily expanded or shrunk by moving the handle in the lower right corner. Structured references (the way of addressing cells/rows/columns in a table) can get tricky, though. Just try -- you might like using tables.

The Group option is something completely different. In brief, it's a convenient way of collapsing and expanding groups of rows and columns.

OK. It looks like I have a bit of reading to do.

Thanks