GETPIVOTDATA

[Erin]

Good afternoon everybody

I need some info on the GETPIVOTDATA function. I have a
report that utilizes it and it only works about 80% of the
time. Else I get #REF.

I have verified that that labels it is looking for do
exist. If I type the labels into the formula manually it
always works, e.g. =GETPIVOTDATA(C7,"'Excedrin
50ct' 'Average Sales'"), but sometimes, it doesn't work
when part of it is a cell reference e.g. =GETPIVOTDATA
(C7,A9 & " Average Sales"). If it never worked with the
cell reference, I would understand, but only certain
values in cell A9 don't work. I know they are exact
matches to the pivot table because they reference the same
source data.

I know this is a little vague, but I can't find a
pattern. If anyone can give me some possible common
issues with this function, that would give me someplace to
start.

Thank you for your help!

Erin

 

[Debra Dalgleish]

For multi word items, include the single quotes. For example:

=GETPIVOTDATA(C7,"'" & A9&"' '" &B8 & "'")
Or
=GETPIVOTDATA(C7,"'" & A9 &"' 'Average Sales'")