Syntax:
GETPIVOTDATA(ValueField, PivotRange [, Field1, Label1, Field2, Label2,...])
Description:
Returns the values of an existing pivot table by retrieving certain fields from the pivot table structure.
Value field: 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 entered with double quotes.
PivotRange: The referenced pivot table. Just address any cell of the pivot table target range to specify it. The argument is entered without quotes.
Field1, Label1, Field2, Label2,...: You can narrow the output of the values by retrieving a specific field in conjunction with an element from this field. It is possible to add several combinations. The argument must be entered with double quotes.
ValueField and PivotRange are required as arguments, Fields/Labels are optional.
Annotation:
For the PivotRange you can also enter a Named range here, but you first have to give the cell range of the pivot table report a name. To do this, 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 assigned as names for the PivotRange.
Example:
Here are example 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, because 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.