How to use the cell reference with GetPivotData

[Mohamed1988]

Hi Team,

I am trying to build relative references to an OLAP cube using the GETPIVOTDATA function "Excel 365".  When I create the initial formula by typing "=", arrowing onto a field in
the cube and pressing enter, I get the below view:

I would like to replace "Dissatisfied" with a relative cell "M$1", also replace "X" with cell "$L1" so it should be working dynamic when I drag to right or down.

Any Help?

Thanks,

[#2 · Accepted solution]

@Mohamed1988 

Within formula you may change

.&[Dissatisfied]",
on
.&[" & M$1 & "]",

[#3]

@Sergei Baklan , at least it works successfully, much appreciated!

[#4]

@Mohamed1988 , you are welcome

[#5]

@Sergei Baklan 

i am having a similar problem but your suggestion doesn't work for me. Can you see what is wrong here?

=GETPIVOTDATA("[Measures].[Sum of Quantity On Hand]",'TWE Pivot'!$A$3,"[Table2].[Item Number]","[Table2].[Item Number].&["&$A3&"]","[Table2].[Description]","[Table2].[Description].&["&$B3&"]")

[#6]

@Nico_Beckers 

To be sure, are you on data model PivotTable or on cached PivotTable? Formula

=GETPIVOTDATA("[Measures].[Sum of Quantity On Hand]",
    'TWE Pivot'!$A$3,
    "[Table2].[Item Number]",
    "[Table2].[Item Number].&[" & A3 & "]",
    "[Table2].[Description]","[Table2].[Description].&[" & B3 & "]")

Please check in second sheet of the attached file.