How do I get GETPIVOTDATA to work?

[Guest]

I'm using the GETPIVOTDATA function in one sheet looking at a Pivot Table in
another sheet in the same workbook. My Pivot Table is a Named Range "MMA_TAC".
Oct-06 is the data Field (Formatted as text), RC and Include Flag are rows,
and Loc is a Column. All I can get is #REF!
=GETPIVOTDATA("Oct-06",MMA_TAC,"RC","1","Include Flag","Y","Loc","04")

I'm using Excel 2002 SP3
Thanks

 

[Debra Dalgleish]

Maybe "1" should be 1.

You could try linking to a pivot table cell, to see the GetPivotData
formula that's automatically created. Then, change the pivot table cell
reference to your named range.

 

[Guest]

Yeah, I had tried the "1" and 1 deal. No change. I got it to work by dropping
one of the fields as follows...
=GETPIVOTDATA("Oct-06",MMA_TAC,"RC","1","Loc","04") I went back to the
spreadsheet and renamed the "Include Field" column to "Field", refreshed the
PT, added "Flag","Y", back into the GETPIVOTDATA formula and it comes back
with an error message. I drop the "Flag","Y", out and it works.
This is how the PT looks...
Loc
RC Flag Data 04 Grand Total
1 Y Count of Tool Code 539.0 539.0
Sum of Oct-06 0.0 0.0
Sum of Nov-06 142.0 142.0
Sum of Dec-06 387.3 387.3

Does your Pivot Tables book have a chapter on the GETPIVOTDATA function?

 

[Debra Dalgleish]

What formula do you get if you type an equal sign, and click on the cell
with the Oct-06 sum, for Loc 04?

The book has about 12 pages on the GETPIVOTDATA function.

 

[Guest]

If I just click the cell references in the GETPIVOTDATA Function Arguments
box, the formula comes out as follows...
=GETPIVOTDATA('PT 737 MMA'!D6,'PT 737 MMA'!A3,'PT 737 MMA'!A4,'PT 737
MMA'!A5,'PT 737 MMA'!B4,'PT 737 MMA'!B5,'PT 737 MMA'!D3,'PT 737 MMA'!D4)
The result is #REF!

 

[Guest]

Ok, We went to your web site and read the deal about the Generate
GetPivotData button. What a great little button. Here's what I get with the
button toggled on...
=GETPIVOTDATA("Sum of Oct-06",'PT 737 MMA'!$A$3,"RC","1","Loc","04","Flag
","Y")
And the great thing is, is that IT WORKS. I'm so happy! Thank you very much!

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.