GETPIVOTDATA with INDIRECT

[MagnusT]

Hi All,

I got a slight problem with Getpivotdata and using indirect. The original formula is:

=+GETPIVOTDATA("Sum of Month 2008 Act",SAGE!$B$64,"Week",Y3)

I now try to use indirect to make "Sum of Month 2008 Act" variable

I have inputted in cell A1 "Sum of Month 2008 Act" and reworked the formula as below but get a #ref error

=+GETPIVOTDATA(Indirect(A1), SAGE!$B$64,"Week",Y3)

Anyone got an idea what I'm doing wrong.

Cheers
Magnus

 

[xenou]

Hi,
The proper syntax needs quotes I think: INDIRECT("F3").
However, it doesn't seem to work either!

These both worked for me adding TEXT() to the formula too:
=GETPIVOTDATA(TEXT($F$3,"@"),$A$3,"b","x")
=GETPIVOTDATA(TEXT(INDIRECT("F3"),"@"),$A$3,"b","x")

GetPivotData is just a finicky little formula, that's all...

Alex.

 

[MagnusT]

Yes, getpivotdata is strange... but your soultion worked! Thanks alot.

Cheers
Magnus

 

[Norie]

Magnus

You shoudn't need INDIRECT.

I mean you aren't using it for Y1.

If anything you might need to wrap A1 in quotes, I don't think you'll need that either.

""" & A1 & """

 

[xenou]

Hi Norie, I tried the concat with the quotes and was surprised it didn't work. I think its something about datatyping since there's a SQL engine doing something internally (i.e., Either you get a numeric value, or if you put it in quotes as text it probably reads the text with the quotes as the text...don't know why but the text function seems to overcome this issue. Maybe concat with a text marker would work too ... ? ... not sure)