Copy data from pivot table using relative reference

[hello]

Hello

I have created a pivot table, and I would like to extract data from it for
further calculations (basically, summing, subtracting, etc the columns that
are in the pivot table). If I use "getpivotdata", or simply refer to the cell
with the "=" sign, excel returns the value in that cell with an absolute
reference. I need relative references, so I can copy down the whole length of
the table. If there is an easier way to do this, that would be nice as well.

Thank you

 

[Roger Govier]

Hi

Just amend the absolute in the formula created by GetPivotData to a
relative as far as the row is concerned
e.g. change $A$5 to $A5.
Sometimes GetPivotData will refer to a particular label, which you may
also need to change to a cell relative reference.

 

[hello]

Roger

Thank you for your help. I think I did not explain fully. If I refer to a
cell in the pivot table (by simply typing =, and clicking on the cell in the
pivot table), here is the formula that excel puts in the cell. Even if I take
out the absolute reference by changing to A3, the rest of the information in
the quotation marks stays, and excel populates all cells with the value in
A3.

=GETPIVOTDATA("Corrected value",'Mar 2007 P'!$A$3,"Series
ID","ces2023610001","Year",1985,"Period","M01")

In the formula above, "Corrected value" is the series in the data area
'Mar 2007 P' is the name of the sheet that contains the pivot table
"Series ID" is the series in the column area
"ces2023610001" is one of variables in "Series ID"
"Year" is the series in the row area
1985 is one of the variables in "Year"
"Period" is another variable in the row area
"M01" is one of the variables in "Period"

If you could provide further advice I would be most grateful.

Thank you

 

[hello]

Wait -- I think I got it. All I have to do is type =, the sheet name, and the
relative cell reference. Hope it works after I refresh all the data..

 

[Roger Govier]

Hi

Supposing you want the Period to alter as you go down the page from Mo1
to Mo2 etc.
and supposing Mo1 is held in cell C5 of your Pivot table, change the
formula to

=GETPIVOTDATA("Corrected value",'Mar 2007 P'!$A$3,"Series
ID","ces2023610001","Year",1985,"Period",C5&"")

Whenever you refer to a cell in the GetPivotData function, it needs a
null character "" either prepended or appended to the cell reference

If the Year is to vary as well, then you would need something like
"year", B5&"",

 

[Debra Dalgleish]

There are instructions here for adding the Generate GetPivotData button
to a toolbar, and toggling the feature on and off:

http://www.contextures.com/xlPivot06.html