How to use the link for the argument GetPivotData Data_Field?

Question

I am trying to use GetPivotData to query for the "Foo Bar" values ​​from a pivot table. Just typing = in a cell and clicking on the pivot table does most of the work, 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, links 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 good #REF! . Hardcoding instead of the field name in the formula instead gives the correct value:

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

Is this an undocumented flaw in the function (all examples use the name of a hard-coded field, but it is not mentioned anywhere that I cannot 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