GETPIVOTDATA(data_field, pivot_table [,field1] [,item1])

Returns the data obtained from a pivot table.

data_fieldThe name of the field you want to retieve data from.
pivot_tableThe reference to a cell in the Pivottable report that contains the data.
field1(Optional) The name of the field that filters the data you want to retrieve.
item1(Optional) The value in the field that filters the data you want to retrieve.

Remarks

* Added in Excel 2000.
* Calculated fields or items and custom calculations are included in this function.
* The "data_field" must be enclosed in quotation marks and is the field that contains the data you want to retrieve.
* The "pivot_table" can be a cell reference, a named range or a label stored in a cell above the PivotTable.
* If "pivot_table" is a range that includes two or more PivotTable reports, data will be retrieved from whichever report was created most recently in the range.
* If "pivot_table" does not refer to a PivotTable, then #VALUE! is returned.
* If "name" describes a single cell, the value of that cell is returned regardless of whether it is a string, number, error, and so on.
* If "name" does not describe a visible field, then #REF! is returned.
* The "name" can include multiple column and row headings with corresponding items in quotation marks, allowing you to retrieve specific data.
* You can have a maximum of 14 pairs of fields and items and they can be in any order.
* For a working example refer to the ?? section.
* For the Microsoft documentation refer to support.office.com

 ABCD
1=GETPIVOTDATA("Sales",named_range,"Name","David","Month","Jan") = 1100NameSalesMonth
2=GETPIVOTDATA("Sales",named_range,"Name","Simon","Month","Jan") = 750David500Jan
3=GETPIVOTDATA("Sales",named_range,"Name","James","Month","Feb") = 1500Simon750Jan
4=GETPIVOTDATA("Sales",named_range,"Name","Simon","Month","Feb") = 850David600Jan
5=GETPIVOTDATA("Sales",named_range,"Month","Jan") = 1850James1500Feb
6=GETPIVOTDATA("Sales",named_range,"Month","Feb") = 2350Simon850Feb
7=GETPIVOTDATA("Sales",named_range,"Name","David") = 1100James650Apr
8=GETPIVOTDATA("Sales",named_range,"Name","Richard","Month","Feb") = 0Richard900Apr
9=GETPIVOTDATA("Sales",named_range) = 7150Simon1400Apr


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top