» Retrieving a Value from a PivotTable Report in Excel 2007

The GETPIVOTDATA function allows the retrieval of a value from a PivotTable report while its structure is being changed.

To retrieve a value, use the GETPIVOTDATA function:

Simply select any cell outside the PivotTable report, press the equals (=) symbol and select a cell in the PivotTable reports Values area.



To clear the option of automatically inserting the GETPIVOTDATA function:

Select File -> Excel Options -> Formulas -> clear the checkbox in Use GetPivotData functions for PivotTable references in Working with Formulas section.

To retrieve a value when the structure of the PivotTable Report is stable, use:

SUMIF function.
Advantage: The SUMIF function recognizes text such as "2003", "Qtr1" or "Grand Total", see cells C1:C3 and results in cells D1:D3 in the screenshot below.