Referencing within a GetPivotData function

[Guest]

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.

 

[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

 

[Guest]

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

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

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.