Self-expanding range

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,560
Office Version
  1. 365
Platform
  1. Windows
I make use of a lot of named ranges. They make the expressions so much easier to read and debug.

I also make use of the Offset function in the Refers to: field in the Name Manager. In the table below, I assigned the name "Products" to Column C (C:C) and "YTD" to Column G. I can then use the formulas in Column G to calculate the totals. If I add a new Column G for Apr, the Sum formulas, now in Column H, will continue to work.

1616171289109.png


An even tidier formula is in D11. I assigned D6 the name "JanHdr" and D10 the name "JanFtr". I then defined the name "Jan" as "=offset(JanHdr,1,0) : offset(JanFtr,-1,0)". This makes the formula in D11 work. And if I add a new product row after Row 9, the formula now in D12 will still work.

Here's my question. If I assign the name "SalesTable" to the entire area (D7:F9), is there a way that I can use that name in a cell and it will use either the column or the row depending on where the cell is?

For example, is there an expression that I can put in D11 that will sum that column (something like "=sum(column(@SalesTable))", or in G7 that will sum that row (=sum(row(@SalesTable))?

Thanks

How do I keep an expression like =offset(JanHdr,1,0):eek:ffset(JanFtr,-1,0) for converting the ": offset" into an emoji?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,391
Although I can conceive of a formula that would do what you want, it would be pretty unwieldy, comparing the row on the sheet with the row in the range. the column on the sheet with the column in the range, and a couple IFs, a SUM and an INDEX.

Have you considered using Tables? Using table references, you can do pretty much everything you've asked about:

Book1
CDEFGH
6ProductsJanFebMarEndYTD
7A25203075
8B10101535
9C56718
10Total403652128
Sheet1
Cell Formulas
RangeFormula
D10D10=SUBTOTAL(109,[Jan])
E10E10=SUBTOTAL(109,[Feb])
F10F10=SUBTOTAL(109,[Mar])
H7:H9H7=SUM([@Jan]:[@End])
H10H10=SUBTOTAL(109,[YTD])


I added an empty End column, so you could get a YTD total and still allow adding columns. You can always hide it. Add the Total row by right clicking a cell in the table, select Table > Totals row. You may need to repeat for each column in the total row.

To prevent the emoji from occurring, surround your formula with the CODE tags. Enter your formula, select it all, then click the fxXLS button in the menu bar.

Excel Formula:
=offset(JanHdr,1,0):offset(JanFtr,-1,0)
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,517
Office Version
  1. 365
Platform
  1. Windows
Personally, I wouldn't call this a solution but it is the closest that I can think of to what you have asked for. As far as I've found so far, @ references only work with single cells.

XL2BB has not picked up the range, SALES which refers to the formula
Excel Formula:
=OFFSET(@Products,,1):OFFSET(@YTD,,-1)
Book1 (version 1).xlsb
CDEFG
6ProductsJanFebMarYTD
7A25203075
8B10101535
9C56718
Sheet4
Cell Formulas
RangeFormula
G7:G9G7=SUM(SALES)
Named Ranges
NameRefers ToCells
Products=Sheet4!$C$7:$C$9G7
YTD=Sheet4!$G$7:$G$9G7
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,560
Office Version
  1. 365
Platform
  1. Windows
Have you considered using Tables? Using table references, you can do pretty much everything you've asked about:

I took a look at tables some time ago, but got distracted and never got into it. I'll take another look.

If I define a table, does it expand if I add rows or columns next to the borders? Or do I have to add dummy rows and columns to make that happen?

Thanks
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
488
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

If you use tables it expands as you add data.

If using named ranges you can add a formula to the named range to make it dynamic

Something like this formula for your named range
=OFFSET($A$2,0,0,COUNTA($A2:$A100),1)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,391
If I define a table, does it expand if I add rows or columns next to the borders? Or do I have to add dummy rows and columns to make that happen?
In a typical table, if you enter data in the row below the table, or in the column to the right of the table, it automatically expands to include that row or column. Any formulas referencing the table will automatically include the new data. However, in this example, where you have the Total row, and a calculated column sum, that won't work. You don't necessarily need to add dummy rows/columns though. I only used one to make the sum work easier. Just adding the row/column should work fine.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,560
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Have you considered using Tables? Using table references, you can do pretty much everything you've asked about:

Table are exactly what I need. Thanks for pushing me in that direction. Now I'm kicking myself for not looking into tables a long time ago. This will save me hours of time and tons of frustration.

Thank you
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,560
Office Version
  1. 365
Platform
  1. Windows
Personally, I wouldn't call this a solution but it is the closest that I can think of to what you have asked for. As far as I've found so far, @ references only work with single cells.

Thanks for taking the time to to post this. But I think I found a solution in Tables.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,560
Office Version
  1. 365
Platform
  1. Windows
If you use tables it expands as you add data.

Yep, I just found that out. I just have to right-click on any cell and then select insert row or column. Slick

If using named ranges you can add a formula to the named range to make it dynamic

Something like this formula for your named range
=OFFSET($A$2,0,0,COUNTA($A2:$A100),1)
I don't understand this part. Does that go in the table definition? I tried something like that and it complained that it did not overlap the existing table, or something like that.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,560
Office Version
  1. 365
Platform
  1. Windows
In a typical table, if you enter data in the row below the table, or in the column to the right of the table, it automatically expands to include that row or column. Any formulas referencing the table will automatically include the new data. However, in this example, where you have the Total row, and a calculated column sum, that won't work. You don't necessarily need to add dummy rows/columns though. I only used one to make the sum work easier. Just adding the row/column should work fine.
Yes, just as you say. And for the total row, I just check the Totals row option in the table definition. Very nice,. Thank you.
 

Forum statistics

Threads
1,141,043
Messages
5,703,917
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top