Refer to Value Cells in GetPivotData Formula

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.

getpivotdataref01

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.

getpivotdataref02

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)

getpivotdataref03

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.

getpivotdataref04

_____________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Functions. Bookmark the permalink.

5 Responses to Refer to Value Cells in GetPivotData Formula

  1. Detlef says:

    Hi Debra

    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.
    Great trick. How did you discover this?

  2. Jeff Weir says:

    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.

  3. Eric says:

    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.

  4. John Hackwood says:

    Really valuable tip Debra thank you and also thanks Eric for your bits….

  5. Radek Kana says:

    You can use T() function. It works as well. T(C1).

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>