Will has attended:
Excel Advanced course
Project Intro Intermediate course
Finance for Non-Financial Managers course
Pivot Tables
In my work I frequently need to write formulae that refer to data kept in Pivot Tables. What is the best way to write a formula that will go and look for a value stored in a pivot table?
RE: Pivot Tables
Hi Will
Thank you for your question.
The function GETPIVOTDATA will allow you to use values from inside a pivot table in a formula created outside of the pivot table area.
Here is an example of how this is used:
In your pivot table, you have Sales as in your data items area and Sum of Sales is showing in cell A3. You have Region in the Row fields area and Quarter in the Column fields area. One of your Regions is North; and you wish to extract the Quarter 2 (Qtr2) figure for the North region.
To extract this value:
Type =GETPIVOTDATA("Sales",$A$3,"Region","North","Quarter","Qtr2")
Note that all text is included inside double quotation marks " "
Alternatively if you are, for example, including other cells outside of the pivot table as part of the formula - lets say you are multiplying the value from the pivot table with another value outside of the pivot table area which is in cell K20 - you can enter =K20* then click on the cell in the pivot table and the GETPIVOTDATA function will automatically be entered as part of the formula for you.
The structure of the GETPIVOTDATA function varies depending on which version of Excel you are using (the above should work in 2002/XP or 2003) however if you have another version it would be a good idea to look up the GETPIVOTDATA function in Help before attempting to use it.
I hope this helps.
Amanda
