GETPIVOTDATA

Syntax:

GETPIVOTDATA ( data-field , pivot-table , field-1 , item-1[ , field-2 , item-2 [ , … ] ] )

Description: Retrieves data stored in a PivotTable report. Calculated fields or items and custom calculations are included in GETPIVOTDATA calculations.

Arguments:

Name

Type

Description

data-field

text

The name of the data field that contains the data to be retrieved.

pivot-table

reference to any cell, range of cells, or named range of cells in a PivotTable report

This information is used to determine which PivotTable report contains the data to be retrieved. If pivot-table is a range that includes two or more PivotTable reports, data shall be retrieved from whichever report was created most recently in the range.

field-1 through field-n

text
Argument pairs field-1 and item-1, field-2 and item-2 through field-n and item-n are field names and item names that describe the data to be retrieved. The pairs can be in any order. Field names and names for items other than dates/times (which shall be expressed as numbers) and numbers shall be enclosed in quotation marks. For OLAP PivotTable reports, items can contain the source name of the dimension as well as the source name of the item.
example: : A field and item pair for an OLAP PivotTable might look like this:
"[Product]","[Product].[All Products].[Foods].[Baked Goods]"

] If the field and item arguments describe a single cell, the value of that cell is returned regardless of its type or value.

item-1 through item-n

text

Return Type and Value: any – The data stored in a PivotTable report.

However, if

  • pivot-table is not a range in which a PivotTable report is found, the return value is unspecified.

  • The arguments do not describe a visible field, the return value is unspecified.

  • The arguments include a page field that is not displayed, the return value is unspecified.

example:

GETPIVOTDATA("Sales",$A$4) returns the grand total of the Sales field, $49,325.

GETPIVOTDATA("Sum of Sales",$A$4) also returns the grand total of the Sales field, $49,325; the field name can be entered exactly as it looks on the sheet, or as its root (without "Sum of," "Count of," and so forth).

GETPIVOTDATA("Sales",$A$4,"Month","March") returns the grand total for March, $30,337.

GETPIVOTDATA("Sales",$A$4,"Month","March","Product","Produce","Salesperson","Buchanan") returns $10,201.

GETPIVOTDATA("Sales",$A$4,"Region","South") is unspecified because the South region data is not visible.

GETPIVOTDATA("Sales",$A$4,"Product","Beverages","Salesperson","Davolio") is unspecified because there is no total value of beverage sales for Davolio.]