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?