Referencing within a GetPivotData function

M

Michael

I am having issues refrencing cells within a the function.

If J1 holds the text "April", I am using:

=GETPIVOTDATA("[Measures].[Hours Worked]",'Project RMT data'!$A$4,"[Project
Text RMT]","[Project Text RMT].[All Project Text
RMT].[Frontier]",INDIRECT("[Time]","[Time].[All Years].[2007].[Q2].["&J1&"]"))

which is returning #VALUE!

however, when I use the same formula ending with ]","[Time].[All
Years].[2007].[Q2].[April]"

the call works correctly.

Thanks for any help with this.
 
B

Bernie Deitrick

Did you try:

=GETPIVOTDATA("[Measures].[Hours Worked]",'Project RMT data'!$A$4,"[Project
Text RMT]","[Project Text RMT].[All Project Text RMT].[Frontier]",]","[Time].[All
Years].[2007].[Q2].[" & J1 & "]")

HTH,
Bernie
MS Excel MVP
 
M

Michael

Thanks, I though I would have to use INDIRECT in some way.

Bernie Deitrick said:
Did you try:

=GETPIVOTDATA("[Measures].[Hours Worked]",'Project RMT data'!$A$4,"[Project
Text RMT]","[Project Text RMT].[All Project Text RMT].[Frontier]",]","[Time].[All
Years].[2007].[Q2].[" & J1 & "]")

HTH,
Bernie
MS Excel MVP


Michael said:
I am having issues refrencing cells within a the function.

If J1 holds the text "April", I am using:

=GETPIVOTDATA("[Measures].[Hours Worked]",'Project RMT data'!$A$4,"[Project
Text RMT]","[Project Text RMT].[All Project Text
RMT].[Frontier]",INDIRECT("[Time]","[Time].[All Years].[2007].[Q2].["&J1&"]"))

which is returning #VALUE!

however, when I use the same formula ending with ]","[Time].[All
Years].[2007].[Q2].[April]"

the call works correctly.

Thanks for any help with this.
Click to expand...
Click to expand...
 
Top