Dynamic Object Pivot Table Reference

[Darkcloud617]

Hello,

I am using a pivot table that is an object so a certain area can be grouped. When I reference a cell in the pivot table it returns this:

=GETPIVOTDATA("[Measures].[Distinct Count of Number]",$AY$119,"[Range].[Audit Period (Month)]","[Range].[Audit Period (Month)].&"[BK119]","[Range].[Loan Type]","[Range].[Type].&[Default]")

The issue is that I need this formula to by dynamic by the "Audit Period" month. I tried to add a cell reference (which contains the month) and that is at cell "[BK119]". In cell "[BK119]" it says "Jan".

Any ideas on adding a dynamic reference to a pivot table formula like this? It would be greatly appreciated.

 

[RoryA]

You just concatenate it in:

=GETPIVOTDATA("[Measures].[Distinct Count of Number]",$AY$119,"[Range].[Audit Period (Month)]","[Range].[Audit Period (Month)].&["&BK119&"]","[Range].[Loan Type]","[Range].[Type].&[Default]")

 

[Darkcloud617]

Thank you for the reply. I seem to be getting a #REF error with that. Is there any other way to concatenate that? In the original the month is reference as "Jan" so I have my cell formatted to do that same.

 

[RoryA]

so I have my cell formatted to do that same

The cell needs to actually contain just the text Jan. If it has a date in it, you'll need to account for that using TEXT:

=GETPIVOTDATA("[Measures].[Distinct Count of Number]",$AY$119,"[Range].[Audit Period (Month)]","[Range].[Audit Period (Month)].&["&TEXT(BK119,"mmm")&"]","[Range].[Loan Type]","[Range].[Type].&[Default]")

 

[Darkcloud617]

That makes total sense. Worked like a charm. Thank you for the assistance.

 

[RoryA]

Glad we could help.