Calculating from values in pivot tables

[Guest]

I want to perform a calculation on data in a pivot table which I then copy
down row by row but when I copy the formula down it does not work as it picks
up the criteria in the first row of the pivot table.

In old versions of Excel it just copied the simple formula down as opposed
to putting in the pivot table criteria - how do I replicate this?

 

[Bernie Deitrick]

Owen,

You need to type the cell reference into the formula rather than using cell selection to pick up the
cell. Clicking on a cell in the pivot table defaults to the GETPIVOTDATA function....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Try Debra's page for a way to turn the GETPIVOTDATA "feature" off:
http://www.contextures.com/xlPivot06.html


---

 

[Roger Govier]

Hi Owen

Whilst the other 2 responses you have are correct, using a cell reference
rather than GetPivotData will result in errors, if the layout of the PT
alters.

You can use the GetPIvotData feature, but having generated the formula,
amend the value where it says the row name, to $A4 (or whatever is the cell
for the first row item you are trying to retrieve, then as you copy down it
will adjust automatically.

Refer to the same reference as Max gave you
http://www.contextures.com/xlPivot06.html
but further down the page, in the section GetPivotData Formula

For example, instead of
=GETPIVOTDATA("Units",$A$3,"Region","Alberta")
you should use
=GETPIVOTDATA("Units",$A$3,"Region",$A5)
In the first case, Alberta would remain a constant as you copy down, in the
second case it would change from Alberta to Ontario to Quebec etc.