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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.