Get Total Amount from Specific Pivot Table

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.

getpivotdataindirect01

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.

getpivotdataindirect02

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)

getpivotdataindirect03

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.

getpivotdataindirect04

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.

____________

This entry was posted in Layout. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *