Self-expanding range

[JenniferMurphy]

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.

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)ffset(JanFtr,-1,0) for converting the ": offset" into an emoji?

 

[rhombus4]

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


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.

Have a look here Dynamic named ranges

Also for tables you can click anywhere in your data and press ctrl + T to insert a table