Using GetPivotData and referncing a cell in another worksheet

[jak82]

Hi,

I am wanting to use getpivotdata to take the value from a pivot table for use in my main worksheet. I have managed to get that to work.

What I am struggling with is I was to be able to use a value in cell (C5) for the year YY ie 16 on the sheet that formula is on. I have tried to reference the cell in the formulae but seem to get #ref

Please find my code below... thanks

 =GETPIVOTDATA("journal_amount",'Pivot Table'!$A$3,"nlyear", 'P&L'!C5,"trans_period","01","account_code","1211")

 

[FormR]

Hi, welcome to the forum

What's in cell C5 - is it just the number 16 or is it a date formatted to show yy?

Can you also post the working formula when it does not reference C5?

 

[jak82]

Hi Thanks Formr,

Just beginning my journey into the world of Excel.

Yes the value in C5 is just the number 16 I want to be abler to change to 15 etc to reference the value in the pivot table. The code that works is

 =GETPIVOTDATA("journal_amount",'Pivot Table'!$A$3,"nlyear","16","trans_period","01","account_code","1211")

Thanks

 

[FormR]

Hi, it looks like the field in the pivot table is text - try this small alteration:

=GETPIVOTDATA("journal_amount",'Pivot Table'!$A$3,"nlyear", 'P&L'!C5&"","trans_period","01","account_code","1211")

 

[jak82]

Works! Many Thanks for the help. Now to try and understand how it works.

It looks like you are referencing the sheet as a string then the cell separately and concatenating it with an empty string.

 

[FormR]

Glad to help

We are referencing the cell in the normal way and concatenating it's value with an empty string. This coerces the returned value to a string instead of a number.