Please help: Reference error in GETPIVOTDATA (pivot table in another sheet)

[Rainbow_P]

Good day!


Can you please help me with a reference question?

A workbook has 2 sheets. Sheet1 is a pivot table.

In sheet2, I use GETPIVOTDATA to return some values from the pivot table in Sheet1.
In sheet2, cells C2 and D2, there are direct formulas when I just clicked the cells (I want to take) from the pivot table in Sheet1.

C2: GETPIVOTDATA("[Measures].[Car count]",Sheet1!$A$6,"[Customer].[Customer ID]","[Customer].[Customer ID].&[ABC Co., Ltd]","[Customer].[Staff]","[Customer].[Staff].&[David]")

D2: GETPIVOTDATA("[Measures].[Car count]",Sheet1!$A$6,"[Customer].[Customer ID]","[Customer].[Customer ID].&[ABC Co., Ltd]","[Product].[Item]","[Product].[Item].&[Fruit]","[Customer].[Staff]","[Customer].[Staff].&[David]")

Now I want to alter the formulas to make it reference the cells in Sheet2 for Customer ID and Staff.

For example in C2, I tried to change the formula to:

GETPIVOTDATA("[Measures].[Vessels Sold To (Distinct)]",Sheet1!$A$6,"[Customer].[Customer Sub Account]",A2,"[Customer].[Account Manager Name]",B2)

But it doesn't work.

What's the right way to do so? Thank you.

 

[FormR]

For example in C2, I tried to change the formula to:

Hi, that looks like a completely different formula to what you said you had that was working in C2.

For the one you said you had working, you could try like this:

=GETPIVOTDATA("[Measures].[Car count]",Sheet1!$A$6,"[Customer].[Customer ID]","[Customer].[Customer ID].&["&A2&"]","[Customer].[Staff]","[Customer].[Staff].&["&B2&"]")

 

[Rainbow_P]

@FormR, thanks for the great help! It works.

btw, I can't find the Edit option that I can edit/update the question. Can you please advise me too? Thank you.

 

[FormR]

I can't find the Edit option that I can edit/update the question

Hi, you only get 10 minutes or so grace period to edit your posts - it might also be a privilege that you only get once you've reached a certain number of posts (not sure about that bit though!) I believe the restrictions are mainly in place to deter spam.

It works.

Great, good to hear!

 

[Rainbow_P]

@FormR, thanks again!

Wish you a great weekend!