Copy GetPivotData Function

[Guest]

I want to copy the GetPivotDataFunction from outside the Report.
When I create the GetPivotData function with the Generate GetPivotData
feature turned on and I trend/copy the function it does not update the cell
reference from the PivotTable Report.

What am I doing wrong?

Thank you again for your help.

Elaine

 

[Bernie Deitrick]

Elaine,

You are doing nothing wrong, it's just that your expectations exceed the
capabilities of that function.

When you use the GETPIVOTDATA function, you can create the initial function
call by typing an equal sign, then selecting a cell within your pivot table.
It will create a function like

=GETPIVOTDATA(Data_Field,Pivot_Table,Field1,Item1)

but those get filled out with constants
=GETPIVOTDATA("Field 1",$A$3,"Unit 1","Fred")

You can change any of the constants to a cell reference

=GETPIVOTDATA("Field 1",$A$3,A4,B4)

and then when you copy it, it will update properly.

Note though, that when you hide or show detail, causing the pivot table to
expand or contract, the references that you use will not properly update.
To get around that, it is better to create a table of key values outside the
pivot table that the GETPIVOTDATA function can reference.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you so much for your help, Bernie. You cleared up my confusion.

Elaine