How to use a reference for GetPivotData's Data_Field argument?

Question

I'm trying to use GetPivotData to query "Foo Bar" values from a pivot table. Just by typing = in a cell and clicking on the pivot table does most of the legwork, as Excel writes most of the formula. For example I get:

=GETPIVOTDATA("Foo Bar", 'Pivot Table'!D1, "Day", DAY(2010,10,10))       → 42

I need to have the first and last argument referenced to other cells and some extra dollars. I tried:

=GETPIVOTDATA($A$1, 'Pivot Table'!$D$1, "Day", $C3)                       → #REF!

...where A1 contains Foo bar and C3 contains =DAY(2010,10,10).

This obviously gives a nice #REF!. Hardcoding the field name in the formula instead gives the correct value instead:

=GETPIVOTDATA("Foo Bar", 'Pivot Table'!$D$1, "Day", $C3)                 → 42

Is this an undocumented shortcoming of the function (all examples use an hardcoded field name, but nowhere is mentioned I can't use references for the first argument) or am I doing something wrong?

Answer 1

To fix this problem, you can concatenate an empty string ( "" ) at the end of the cell reference:

=GETPIVOTDATA(B10 & "" ,$B$3,"Region",A11)`

from http://www.contextures.com/xlpivot06.html