DYNAMIC NAMED RANGE
I have tried to get this to work using instructions from Dave Hawley’s site and from instructions posted here. The most recent being by Aladin (below)
The range expands if you insert a row in the range but an ordinary named range will do that. A pivot table will not see anything added to the bottom of the table. What am I doing wrong?
[1] activate the option Insert|Name|Define,
[2] enter EndRow as name in the Names in the Workbook box,
[3] enter as formula in the Refers to box:
=MATCH(9.99999999999999E+307,x!$A:$A)
[ Note. Replace 'x' by the sheetname where the database is. ]
[4] Activate Add,
[5] Enter Database as name in the Names in Workbook box,
[6] Enter as formula in the Refers to box:
=OFFSET(x!$A$1,0,0,EndRow,5)
[ Note. Replace 'x' by the sheetname where the database is. ]
[7] Activate OK.
Now, you're ready to use the dynamic range name, Database in your workbook. It will expand or shrink automatically along with deletions from or the additions to the data area.
I have tried to get this to work using instructions from Dave Hawley’s site and from instructions posted here. The most recent being by Aladin (below)
The range expands if you insert a row in the range but an ordinary named range will do that. A pivot table will not see anything added to the bottom of the table. What am I doing wrong?
[1] activate the option Insert|Name|Define,
[2] enter EndRow as name in the Names in the Workbook box,
[3] enter as formula in the Refers to box:
=MATCH(9.99999999999999E+307,x!$A:$A)
[ Note. Replace 'x' by the sheetname where the database is. ]
[4] Activate Add,
[5] Enter Database as name in the Names in Workbook box,
[6] Enter as formula in the Refers to box:
=OFFSET(x!$A$1,0,0,EndRow,5)
[ Note. Replace 'x' by the sheetname where the database is. ]
[7] Activate OK.
Now, you're ready to use the dynamic range name, Database in your workbook. It will expand or shrink automatically along with deletions from or the additions to the data area.