JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,691
- Office Version
- 365
- Platform
- Windows
Suppose I have a table like the one below. As it stand now, the data is in D6:G11. I want to define a dynamic named range ("Table") that is, at the moment, D5:G11. If I add a row anywhere between 5 and 12 or a column anywhere between C and H, I want Table to automatically expand.
[TABLE="class: grid, width: 448"]
<tbody>[TR]
[TD="width: 64, align: center"]R/C[/TD]
[TD="width: 64, align: center"]C[/TD]
[TD="width: 64, align: center"]D[/TD]
[TD="width: 64, align: center"]E[/TD]
[TD="width: 64, align: center"]F[/TD]
[TD="width: 64, align: center"]G[/TD]
[TD="width: 64, align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Hd/C1
[/TD]
[TD="align: center"]TblHead[/TD]
[TD="align: center"]TblHead[/TD]
[TD="align: center"]TblHead[/TD]
[TD="align: center"]TblHead[/TD]
[TD="align: center"]Hd/CN[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]TblCol1
[/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Price
[/TD]
[TD="align: center"]Weight
[/TD]
[TD="align: center"]$$/lb[/TD]
[TD="align: center"]TblColN
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]5/01/19 [/TD]
[TD="align: center"]$25.00 [/TD]
[TD="align: center"]5.0 lb [/TD]
[TD="align: center"]$5.00 [/TD]
[TD="align: center"]TblColN
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]4/22/19 [/TD]
[TD="align: center"]$50.00 [/TD]
[TD="align: center"]12.0 lb [/TD]
[TD="align: center"]$4.17 [/TD]
[TD="align: center"]TblColN[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]3/14/19 [/TD]
[TD="align: center"]$12.50 [/TD]
[TD="align: center"]4.0 lb [/TD]
[TD="align: center"]$3.13 [/TD]
[TD="align: center"]TblColN[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]5/03/19 [/TD]
[TD="align: center"]$99.00 [/TD]
[TD="align: center"]30.0 lb [/TD]
[TD="align: center"]$3.30 [/TD]
[TD="align: center"]TblColN
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]4/07/19 [/TD]
[TD="align: center"]$35.00 [/TD]
[TD="align: center"]7.5 lb [/TD]
[TD="align: center"]$4.67 [/TD]
[TD="align: center"]TblColN[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]3/30/19 [/TD]
[TD="align: center"]$19.95 [/TD]
[TD="align: center"]5.0 lb [/TD]
[TD="align: center"]$3.99 [/TD]
[TD="align: center"]TblColN[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Ft/C1[/TD]
[TD="align: center"]TblFoot
[/TD]
[TD="align: center"]TblFoot[/TD]
[TD="align: center"]TblFoot[/TD]
[TD="align: center"]TblFoot[/TD]
[TD="align: center"]Ft/CN[/TD]
[/TR]
</tbody>[/TABLE]
For example, if I add a row between 5 & 6, then Table will become D5:G12. If I then add a column between G & H, Table will become D5:H12. And so on...
I have defined these 4 named ranges:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]TblHead[/TD]
[TD]$4:$4[/TD]
[/TR]
[TR]
[TD]TblFoot[/TD]
[TD]$12:$12[/TD]
[/TR]
[TR]
[TD]TblCol1[/TD]
[TD]$C:$C[/TD]
[/TR]
[TR]
[TD]TblColN[/TD]
[TD]$H:$H[/TD]
[/TR]
</tbody>[/TABLE]
These form a border around the table area (shown in red). I am able to access the upper-left & lower-right corners of my target table with these expressions.
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]R/C[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]C15: =OFFSET(TblCol1 TblHead,1,1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3.99[/TD]
[TD]C16: =OFFSET(TblColN TblFoot,-1,-1)[/TD]
[/TR]
</tbody>[/TABLE]
Next I tried to combine these expressions into a dynamic named range. In Name Manager, I assign this string to the name "Table":
Then I try to select it using Ctrl+g, but Table is not listed. When I reopen name manager, I see that it exists, but the string has been changed to:
"Dynamic Table" is the name of the sheet.
What am I doing wrong?
[TABLE="class: grid, width: 448"]
<tbody>[TR]
[TD="width: 64, align: center"]R/C[/TD]
[TD="width: 64, align: center"]C[/TD]
[TD="width: 64, align: center"]D[/TD]
[TD="width: 64, align: center"]E[/TD]
[TD="width: 64, align: center"]F[/TD]
[TD="width: 64, align: center"]G[/TD]
[TD="width: 64, align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Hd/C1
[/TD]
[TD="align: center"]TblHead[/TD]
[TD="align: center"]TblHead[/TD]
[TD="align: center"]TblHead[/TD]
[TD="align: center"]TblHead[/TD]
[TD="align: center"]Hd/CN[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]TblCol1
[/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Price
[/TD]
[TD="align: center"]Weight
[/TD]
[TD="align: center"]$$/lb[/TD]
[TD="align: center"]TblColN
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]5/01/19 [/TD]
[TD="align: center"]$25.00 [/TD]
[TD="align: center"]5.0 lb [/TD]
[TD="align: center"]$5.00 [/TD]
[TD="align: center"]TblColN
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]4/22/19 [/TD]
[TD="align: center"]$50.00 [/TD]
[TD="align: center"]12.0 lb [/TD]
[TD="align: center"]$4.17 [/TD]
[TD="align: center"]TblColN[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]3/14/19 [/TD]
[TD="align: center"]$12.50 [/TD]
[TD="align: center"]4.0 lb [/TD]
[TD="align: center"]$3.13 [/TD]
[TD="align: center"]TblColN[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]5/03/19 [/TD]
[TD="align: center"]$99.00 [/TD]
[TD="align: center"]30.0 lb [/TD]
[TD="align: center"]$3.30 [/TD]
[TD="align: center"]TblColN
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]4/07/19 [/TD]
[TD="align: center"]$35.00 [/TD]
[TD="align: center"]7.5 lb [/TD]
[TD="align: center"]$4.67 [/TD]
[TD="align: center"]TblColN[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]TblCol1[/TD]
[TD="align: center"]3/30/19 [/TD]
[TD="align: center"]$19.95 [/TD]
[TD="align: center"]5.0 lb [/TD]
[TD="align: center"]$3.99 [/TD]
[TD="align: center"]TblColN[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Ft/C1[/TD]
[TD="align: center"]TblFoot
[/TD]
[TD="align: center"]TblFoot[/TD]
[TD="align: center"]TblFoot[/TD]
[TD="align: center"]TblFoot[/TD]
[TD="align: center"]Ft/CN[/TD]
[/TR]
</tbody>[/TABLE]
For example, if I add a row between 5 & 6, then Table will become D5:G12. If I then add a column between G & H, Table will become D5:H12. And so on...
I have defined these 4 named ranges:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]TblHead[/TD]
[TD]$4:$4[/TD]
[/TR]
[TR]
[TD]TblFoot[/TD]
[TD]$12:$12[/TD]
[/TR]
[TR]
[TD]TblCol1[/TD]
[TD]$C:$C[/TD]
[/TR]
[TR]
[TD]TblColN[/TD]
[TD]$H:$H[/TD]
[/TR]
</tbody>[/TABLE]
These form a border around the table area (shown in red). I am able to access the upper-left & lower-right corners of my target table with these expressions.
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]R/C[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]C15: =OFFSET(TblCol1 TblHead,1,1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3.99[/TD]
[TD]C16: =OFFSET(TblColN TblFoot,-1,-1)[/TD]
[/TR]
</tbody>[/TABLE]
Next I tried to combine these expressions into a dynamic named range. In Name Manager, I assign this string to the name "Table":
Code:
=OFFSET(TblCol1 TblHead,1,1):OFFSET(TblColN TblFoot,-1,-1)
Code:
=OFFSET('Dynamic Table'!TblCol1 'Dynamic Table'!TblHead,1,1):OFFSET('Dynamic Table'!TblColN 'Dynamic Table'!TblFoot,-1,-1)
What am I doing wrong?