Self-expanding range

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,488
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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)
 
Upvote 0
Solution
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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
Back
Top