Named Ranges in Pivot table

[maabbot]

Hi

I have a sample of sales data which I have dynamically named according to criteria. I have also dynamically named the whole data set. When creating a pivot table I cannot seem to use the the name of the raw data set (or dynamic naing formula) when asked to input the data range to be used.

Does anyone have any advice?

Thanks

 

[PaddyD]

did you try a search? here's one of the many posts re dynamic pivot tables:

http://www.mrexcel.com/board/viewtopic.php?topic=6534&forum=2

Paddy

 

[GeorgeB]

Maabbot
Here's how.

DYNAMIC NAMED RANGE

[1] Activate 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 with the sheetname where the database is. ]

Example: If the name of the sheet is INPUT
=MATCH(9.99999999999999E+307,INPUT!$A:$A)

If you don’t have numbers (amounts) in column $A:$A then change that reference to a column that does.

Example:
=MATCH(9.99999999999999E+307,INPUT!$C:$C)

[4] Activate Add,
[5] Enter Database as name in the Names in Workbook box,
(Just type over what is there)
[6] Enter as formula in the Refers to box:
(Again type over or alter what is there)

=OFFSET(INPUT!$A$1,0,0,EndRow,5)
(5 indicates the number of columns in the table. Alter to suit.)

[ Note. Replace 'INPUT' with the sheetname where your database is. ]

If your database does not start in row one change the formula.
For example if your database starts in row 10 then subtract 9 from the formula and change the first part to reflect where the top left corner of the database is.

Example
=OFFSET(INPUT!$A$10,0,0,EndRow-9,5) (Again change the sheet name.)

Remember the database starts in the row where the headings are.

[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. When used with a Pivot Table it will update automatically upon Refresh Data.

NOTE: You must create the Pivot Table AFTER you create the Dynamic Range.

Enjoy