Getpivotdata - referncing a data_field resulting in "REF!
 

Hi,

Im using GETPIVOTDATA(data_field,pivot_table,field1,item1,f ield2,item2,...)

and it works just fine until I try and reference a cell for
"data_field,pivot_table".

If I type the name of the data_field into the formula it works fine when
referenceing a cell I get #REF!

I might ad that anywhere else in the formula I am able to reference cell


for example

This works
=GETPIVOTDATA("New Subscribers",$A$3,E1,I$4+1,"WeekDay",$G9)

This doesnt
=GETPIVOTDATA(E3,$A$3,E1,I$4+1,"WeekDay",$G9) where cell E3 contanins the
text : New Subscribers

Interestingly this works too

=GETPIVOTDATA(A1 & E3,$A$3,E1,I$4+1,"WeekDay",$G9) where cell E3 contanins
the
text : New Subscribers and A1 is a blank cell.

I can't explain why, but if you append an empty string at either end of
the cell reference, the formula should work, e.g.:

=GETPIVOTDATA(E3&"",$A$3,E1,I$4+1,"WeekDay",$G9)
or
=GETPIVOTDATA(""&E3,$A$3,E1,I$4+1,"WeekDay",$G9)


Peter wrote:
Hi,

Im using GETPIVOTDATA(data_field,pivot_table,field1,item1,f ield2,item2,...)

and it works just fine until I try and reference a cell for
"data_field,pivot_table".

If I type the name of the data_field into the formula it works fine when
referenceing a cell I get #REF!

I might ad that anywhere else in the formula I am able to reference cell


for example

This works
=GETPIVOTDATA("New Subscribers",$A$3,E1,I$4+1,"WeekDay",$G9)

This doesnt
=GETPIVOTDATA(E3,$A$3,E1,I$4+1,"WeekDay",$G9) where cell E3 contanins the
text : New Subscribers




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html