Excel - Confused about GetPivotData Function

[mleosu]

I have several pivot tables that I use to summarize the data I need and then, currently, I am using vlookups to transfer the pivot table data to reports. This creates error sometimes - becuase the pivot tables can change each time I run the report - so I have to make sure that the vlookup is searching the entire pivot table range.

I think I understand the GetPivotData function, but what confuses me is that my pivot tables have only calculated data. I have attached an example of a pivot I use. I am not sure how to retrieve the data. I need to retrieve the region data for each category and I also need to retrieve the grand total for each category.

I think if this was a basic pivot then I would be able to write a working GetPivotData function - but of course...

EXAMPLE:

http://farm1.static.flickr.com/191/500929974_7f80de55fb_o.jpg

 

[SkipVought]

Hi,

Put the Reg field in a PAGE field. Select values one at a time.

You could also look at the DGET, or DSUM functions.

Skip,

_[red][/red]

 

[AnotherHiggins]

Bear in mind that GetPivotData only exists in Excel 2002 and later. If you have anyone with 2000 who will be looking at this, their version of Excel won't know what to do with that function. That might not be an issue in your office, but it is in mine....

I've used lookups in the past - just used a Dynamic Named Range so that it sizes with the pivot table. See

http://tek-tips.com/faqs.cfm?fid=1331

for info on creating a Dynamic Named Range.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mleosu]

Oh - I didnt realize that about the Excel versions... that will be a problem since our office ranges from Excel 97 to 2003!!

Thanks so much, I'll look into the dynamic name ranges!

 

[AnotherHiggins]

Glad I mentioned it, then!

Feel free to start up a new thread with any questions if you have trouble with the dynamic ranges. It's confusing the first time you do it, but once you get the hang of it they can be a real lifesaver.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.