Pivot tables & dynamic named ranges

**WillysK5** · 06-05-2008, 04:14 PM

I figured out how to create a dynamic named data range I would like to use for a pivot table, but when creating the pivot table, how do you tell it to use that named range?

I got the info from http://www.contextures.com/xlNames01.html

Some background: I've been playing with pivot tables a little lately, but somehow, when I'm changing the reference data by removing or adding rows, when I update the pivot table I get an error that says it's only referencing 1 row. I hope this dynamic named data range thing fixes that. Please let me know if it will not.

Thanks!

**VBA Noob** · 06-05-2008, 04:21 PM

The arguments on Debra site assumes the below

Please Login or Register  to view this content.

To use
1) Change Sheet1 references to your sheet name.
2) Change Cell A1 to your First cell
3) It assumes Column A has no blanks and Row 1 has no blanks
4) You type in NameList (or your named range) in the pivot table wizard as range

HTH..... if not post a sample workbook with your layout

VBA Noob

**WillysK5** · 06-05-2008, 04:32 PM

fer cryin' out loud....

I forgot the '=' in front of the range name when referencing the pivot table.

=OFFSET(MasterJobList!$A$2,0,0,COUNTA(MasterJobList!$A:$B),20) is called pivottableref.

When I create the pivot table, I type in =pivottableref and it works fine.

Thanks for joggin' those brain cells VBA noob.