If you have multiple copies of a pivot table in a workbook, on different sheets, you can use the GETPIVOTDATA function to pull the total amount from a specific pivot table. In this example, there are 3 copies of the pivot table in a workbook, each with a different layout and filter applied.
The sheets are named consistently, starting with “PT_”, and the pivot table location is the same on each sheet.
Select a Region
On the main sheet, there is a drop down list of regions – All, East and North. This list was created with data validation, and is based on the named range, RegionList.
To start, select the East region from the drop down list.
Create a GETPIVOTDATA Formula
To pull the total amount from the pivot table on the PT_East sheet, you can simply link to the Grand Total cell on that sheet. Unless you have turned off the Generate GETPIVOTDATA feature, Excel will automatically create the GETPIVOTDATA formula for you.
To get the total:
- Select cell D6
- Type an equal sign
- Click on the PT_East sheet, then click on the Grand Total cell, and press Enter
The following formula appears in cell D6, and the result is the Grand Total for the East region.
=GETPIVOTDATA(“TotalPrice”,PT_East!$B$4)
Create a Reference with INDIRECT
Instead of leaving the hard-code reference to the PT_East sheet, you can use the INDIRECT function to create a reference from a text string, using the region name selected in cell C6.
To create a sheet name and cell reference, you could use this formula:
=”PT_” & C6 & “!$B$4”
When used as an argument in the INDIRECT function, that string will create a reference to cell B4 on the selected region’s sheet.
INDIRECT(“PT_” & C6 & “!$B$4”)
Generalize the Formula with INDIRECT
So, to generalize the formula, you can replace the current sheet reference with the INDIRECT formula:
=GETPIVOTDATA(“TotalPrice”,PT_East!$B$4)
changes to:
=GETPIVOTDATA(“TotalPrice”,INDIRECT(“PT_” & C6 & “!$B$4”))
Now, when you select a different region, the total amount will automatically change.
Video: Get Total Amount from Specific Pivot Table
Watch this video to see the steps for getting the total from a specific pivot table, with GETPIVOTDATA and INDIRECT.
Or watch on YouTube: Get Totals from Specific Pivot Table With GetPivotData
Download the Sample File
For more GETPIVOTDATA function examples, and to download the sample file for this tutorial, please visit the GETPIVOTDATA function page on my Contextures website. The zipped sample file is in xlsx format, and does not contain macros.
____________