Leading the way in Microsoft Office Development
 Home|Excel|Word|PowerPoint|Consultancy|Feedback|Contact 
 Microsoft Excel > Functions > Lookup and Reference > GETPIVOTDATA
 

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

 
 Returns data that is stored and visible in a Pivottable report.

 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.
 field1The name of the field that filters the data you want to retrieve.
 item1The value in the field that filters the data you want to retrieve.

 REMARKS
 
  		• 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 please refer to the Pivot Table section.

     EXAMPLES
     
     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
    		 
     Functions - G | Index - G | Office Online 

     Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.Top