JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,488
- Office Version
- 365
- Platform
- 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.
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?
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?