getpivotdata

[Hagar Speaks]

Hello, thanks for any assistance.

I'm have a pivot table on one worksheet, which pulls from the same workbook. However, I am attempting to pull additional detail broken down by location on a separate worksheet (to show detail by location).

I have tried to use the getpivotdata formula, but keep receiving a error. My data range on the pivot table (separate sheet within workbook) is "Count of Complete", "Team", "Location" ...several other headers I do not want to pull from.. and, "Grand Total".

I'm attempting to use getpivotdata to tell me what the grand total is by manager. Remember this is on the same workbook, different worksheet titled "pivot"

Any help is greatly appreciate!

 

[cyrilbrd]

Hi,
what version of Excel are you using?
What kind of error?

 

[Hagar Speaks]

Morning.. 2003. The error is #REF!

The formula I used is:

=GETPIVOTDATA(Pivot!B1,Pivot!B2,Pivot!C2,Pivot!C3,GETPIVOTDATA("Complete",Pivot!$B$1,"Location",2293,"DCL_CD",3))

Thanks in advance.

 

[cyrilbrd]

Hi,
Sorry but I might have misunderstood your first post. Are you using two different sheet as raw data for your pivot table?
Did you use the option "Multiple Consolidation Ranges"?

 

[Hagar Speaks]

Hello.. I'm trying to setup a pivot table to feed a worksheet. Basically what I have is a pivot table with a grand total under each manager. That is driven by a pivot. I created a new worksheet, same workbook, that I want to feed the grand total into each week. One column for each week that I can move over, refresh the pivot and feed the new week's data.

I thought you may have been on to something with the multiple consolidation ranges but tried it, and found you have to have the same headers.

Should a vlookup do this? I thought getpivotdata would be the answer but havn't gotten anywhere.

 

[cyrilbrd]

Yes vlookup of index/match might give you what you need without the hassle of the pivot.

Sorry for the delay.