DYNAMIC NAMED RANGE

GeorgeB

Board Regular
Joined
Feb 16, 2002
Messages
239
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
It should after a refresh.

Is your column A of numeric type?
This message was edited by Aladin Akyurek on 2002-05-03 09:45
 
Upvote 0
Yes indeed. this is a table containing Names, Places and amounts. A credit card expenditure report.
 
Upvote 0
On 2002-05-03 11:21, GeorgeB wrote:
Yes indeed. this is a table containing Names, Places and amounts. A credit card expenditure report.

George,

Just switch from A to the amount column (probably C in your case) in

=MATCH(9.99999999999999E+307,x!$A:$A)

you'll be OK.

Aladin
 
Upvote 0
On 2002-05-03 12:07, GeorgeB wrote:
Do you highlight a specific range or cell? If so which?

No. No need for that. Just try to follow the steps you quoted. Don't forget using the sheet name of the worksheet where your data is.

By the way, 5 in

=OFFSET(x!$A$1,0,0,EndRow,5)

refers to the number of columns the data area of interest covers: The above formula covers columns A to E, E being the 5th column.
This message was edited by Aladin Akyurek on 2002-05-03 12:32
 
Upvote 0
One more problem.
It worked well on a test book but when I tried to put it to use on the real workbook the pivot table selected 59 rows below the end of the table.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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