Copy GetPivotData Function

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


--
Thank you... Elaine

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


"Elaine" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> --
> Thank you... Elaine

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

Elaine

"Bernie Deitrick" wrote:

> 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
>
>
> "Elaine" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > --
> > Thank you... Elaine
>
>
>