Syntax:
GETPIVOTDATA(ValueField, PivotRange [, Field1, Label1, Field2, Label2,...])
Description:
This returns the values of an existing pivot table by retrieving certain fields from the pivot table structure.
Value field: This is the name of the field that you have placed in the Values area of the pivot table and whose data you want to retrieve. The argument must be specified within double quotation marks.
PivotRange: The referenced pivot table. Just address any cell of the pivot table target range to specify it. The argument is specified without quotation marks.
Field1, Label1, Field2, Label2,...: You can narrow the output of the values by retrieving a specific field in conjunction with an item from this field. You can add several combinations. The argument must be specified within double quotation marks.
ValueField and PivotRange are required as arguments, while Fields/Labels are optional.
Additional info:
For the PivotRange, you can also enter a Named range, but you first have to assign a name to the cell range of the pivot table report. To do so, select a cell in the pivot table report (you do not have to select the entire range) and follow the description in the Naming cell ranges section.
You cannot use the name automatically generated by PlanMaker (e.g., "Pivot table 1") as the Named range. Only names that have been created via the ribbon command Formula | group Named areas | Edit names can be actually assigned as names for the PivotRange.
Example:
Here is some sample data from the Pivot tables section. Assuming your pivot table report is in cell range F1:G12 and the "Amount" field is in the Values area, the "Employee" and "Order" fields are in the Row labels area:
GETPIVOTDATA("AMOUNT", F1) returns 200, since the grand total for all values of Amount is displayed.
GETPIVOTDATA("AMOUNT", F1, "Employee", "Anna") returns 123, since the result of Amount is restricted to the Employee field containing the item Anna.
GETPIVOTDATA("AMOUNT", F1, "Employee", "Anna", "Order", "Coffee") returns 75, since the result of Amount is restricted to the field Employee/item Anna in combination with the field Order/item Coffee.