ExcelBanter - Referencing within a GetPivotData function

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Referencing within a GetPivotData function (https://www.excelbanter.com/excel-worksheet-functions/147455-referencing-within-getpivotdata-function.html)

Michael

Referencing within a GetPivotData function

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

Referencing within a GetPivotData function

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" wrote in message
...
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.

Michael

Referencing within a GetPivotData function

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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.

All times are GMT +1. The time now is 04:47 AM.