When you type an equal sign, then click on a pivot table value cell, a GetPivotData formula is automatically created. We’ll see how to make those formula more flexible, so you can quickly change the results.
For example, in the screen shot below, I typed an equal sign in cell A1, then clicked on the Arrowroot total price in the pivot table.
Excel created a GetPivotData formula, and shows “Total Price” as the first argument:
=GETPIVOTDATA(“Total Price”,$A$3,”Product”,”Arrowroot”)
Change Text to Cell References
To make the formula flexible, we can change the text strings to cell references, and you can see sample formulas here.
For example, type Arrowroot in cell B1, then change the formula so it refers to that cell for the product name.
=GETPIVOTDATA(“Total Price”,$A$3,”Product”,B1)
Then, you can change cell B1 to a different product, and the formula result changes automatically. Now we can see the total price for Carrot in cell A1.
Problem with Value Cell Reference
Cell references work well with the row fields, but if you try to use the same technique with the Value field name – Total Price – the formula returns a #REF! error.
In the screen shot below, I entered Total Price in cell C1, and referred to that cell. The formula returns a #REF! error, even though cell C1 contains the correctly typed field name, “Total Price”.
=GETPIVOTDATA(C1,$A$3,”Product”,B1)
Fix the Value Field Cell Reference
If you want to use a cell reference for a value field, use the & operator to concatenate an empty string at the start or end of the reference.
In this example, you could use
=GETPIVOTDATA(C1 & “”,$A$3,”Product”,B1)
or
=GETPIVOTDATA(“” & C1,$A$3,”Product”,B1)
The formula shows the correct total for Total Price.
Then, I can change the Value field name in cell C1 to Total Qty, and it will show that amount.
_____________________
Hi Debra
Great trick. How did you discover this?
Awesome post, once again, Deb. Didn’t know that trick. Thanks for posting it. Real pity that you can’t refer to row fields with the GetPivotData function.
These also worked:
Trim(C1) — Dangerous in the case you actually have white space as part of the field
Cell(“contents”, C1)
Then I tried a little UDF:
Function CText(val As String) As String
CText = val
End Function
so
CText(C1)
also worked.
Really valuable tip Debra thank you and also thanks Eric for your bits….
You can use T() function. It works as well. T(C1).
along the lines of using a cell reference for the “value”, is there a trick to using such a technique for the “item”? In the top example it showed “product” and then it pointed to B1 where the user could select different ones. But I would like to make it even more flexible where the user can pick what to filter on (like, Product or Region or Month, etc) and then also pick the value to look for. But anything I try gives the #REF! error. Is there no way to dynamically tell it to look for a combination of item and value?
@Kurt, you could use a cell reference for the field name too, like Product or Region.
In the example in the blog post, you could refer to Product in cell A4
=GETPIVOTDATA(“” & C1,$A$3,A4,B1)
However, the GetPivotData formula will show a #REF! error if that field is not already in the pivot table layout
We had luck with encapsulated cell values and Power Pivot tables
=GETPIVOTDATA(“Total Price”,$A$3,”Product”,&[“&B1&”]”)
In our example, the syntax looks like this:
=GETPIVOTDATA(“[Measures].[Sum of TOTAL_AMT_ORDERED]”,OpenOrderPP!$B$3,”[OpenOrders].[MonthOrd]”,”[OpenOrders].[MonthOrd].&[1]”,”[OpenOrders].[CustGroup]”,”[OpenOrders].[CustGroup].&[“&B12&”]”
Thanks David!
Thanks!!! exactly what i needed
I’ve been trying to fix my formula for about and hour with nothing working (doesn’t help that I’m looking up a field that is looking up a value field and so on and so on…)
And this is the only thing that managed to finally fix it!
Thanks! 🙂
This is great! Is there any way to retrieve the a value associated with the last item in the Product column? i.e. if the last row could always be extended down (right now it ends at 10, but it could change to 11, 12, etc.), and I wanted to reference the Total Value associated with the last item in that column.
I hope this makes sense and thanks for your help!
Brilliant, thank you.
Thanks, been struggling with this for a few hours and could not figure out why the cell reference was not working.
Thanks Rob, and I’m glad the article helped you. It’s a pretty obscure problem!