GETPIVOTDATA

This function returns visible data from a Pivot Table.

Syntax

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

Arguments

This function has these arguments:

Remarks

• Calculated fields or items and custom calculations can be included in GETPIVOTDATA calculations.

• If the pivot_table argument is a range that includes two or more PivotTables, data will be retrieved from whichever PivotTable was created most recently.

• If the field and item arguments describe a single cell, then the value of that cell is returned regardless of whether it is a string, number, error, or blank cell.

• If an item contains a date, the value must be expressed as a serial number or populated by using the DATE function so that the value will be retained if the worksheet is opened in a different locale. For example, an item referring to the date March 5, 1999 could be entered as 36224 or DATE(1999,3,5). Times can be entered as decimal values or by using the TIME function.

• If the pivot_table argument is not a range in which a PivotTable is found, GETPIVOTDATA returns #REF!.

• If the arguments do not describe a visible field, or if they include a report filter in which the filtered data is not displayed, GETPIVOTDATA returns the #REF! error value.

Data Types

Returns visible data from a pivot table.

Examples

The following code sample shows the usage of the GETPIVOTDATA function.

C#

sheet2.Cells["K1"].Formula = "GETPIVOTDATA(\"Amount\",$A$1)";
sheet2.Cells["K2"].Formula = "GETPIVOTDATA(\"Amount\",$A$1,\"Country\",\"Canada\")";

VB

sheet2.Range("K1").Formula = "GETPIVOTDATA(""Amount"", $A$1)"
sheet2.Range("K2").Formula = "GETPIVOTDATA(""Amount"", $A$1, ""Country"", ""Canada"")"