Getpivotdata function error

[Bryon Scruggs]

I have a getpivotdata formula which is returning the #REF!
error. This occures when I replace the "item1 field" from
a hard coded value to a cell reference.

Example - The A46 is my cell reference in this case.
=GETPIVOTDATA("surveyid",'Vendor file pivots'!
$D$4,"surveyid",A46)

The pivot table source data is SQL table.

I suspect this is caused by fact that the row field
(called surveyid in the example below) is a number, but I
can't fix the problem by changing the format of both the
pivot table and cell reference fields to the same format.

Count of surveyid
surveyid Total
10 2
100 46
102 6
103 8
104 4


thanks,

Bryon Scruggs

 

[Debra Dalgleish]

Try converting the reference to a number in the formula, by adding zero:

=GETPIVOTDATA("surveyid",'Vendor file pivots'!$D$4,"surveyid",A46+0)