Pivot Table Pull – GETPIVOTDATA

One of the beautiful things about pivot tables is that your source data can change as much as it wants.  All you have to do is Refresh your pivot table and you get the new result(s) in an instant.  If you have your pivot table set up so that it is the report itself (as discussed in our previous postings on pivot tables), then there is nothing more required than to use the Refresh button.  However, sometimes it may be that you only need the total from the pivot table.  In such a case in the past, you may simply have used the pivot table to calculate the result and then manually hard-coded that result into the cell where you really needed the number.  No longer!

Wouldn’t it be nice if you could hit the Refresh button, have your pivot table update the result and then have that result automatically appear in the cell where you need it?  Occasionally, you might be able to get away with simply referring to the cell your total appears in, for example, by creating the formula:  =C19 to refer to the Grand Total shown below.  However, if you’ve got a pivot table like the one below, then each month when you refresh the information your total will move down a row…and then, really, what’s the point?  You’ve always got to go in and change the reference anyway, so you might as well hard-code the number!

Now, the above pivot table is already set up to be its own report, but let’s say we want to pull the Grand Total result into a different report as well.  Once we know where we want to pull the total in, all we need to do is hit the equals key (i.e., =) on our keyboard to start the formula, switch to the tab that has our pivot table, and click on the cell that has our total.  This will cause a GETPIVOTDATA formula to appear in our cell.  If we hit the Enter key, we should immediately see the result of 3,635,000 in that cell, as shown below.

Notice that the formula appears in the formula bar above our sheet (indicated by the red box).  Your formula should look something like this, but we’ll get into more of the details in the next few days about how the GETPIVOTDATA formula is set up.  Regardless, if we were to change any of the source data that pulls into our pivot table on the other sheet, we could then Refresh the pivot table and the value in B6 on this sheet above would change at the same time.  Very handy for on-going monthly reporting and analysis, wouldn’t you agree?