Getpivotdata error

[PATSYS]

Hi all,

This formula works well:

=GETPIVOTDATA("Sum of 1-Jan",'[Working File.xlsx]BUD2016-Pivot'!$A$5,"CC code",$D$2,"Field1",$C12)

However when I change it to:

=GETPIVOTDATA($Z$2,'[Working File.xlsx]BUD2016-Pivot'!$A$5,"CC code",$D$2,"Field1",$C12)

I get #REF!

Cell $Z$2 has a value Sum of 1-Jan

Any idea why?

Thanks in advance.

 

[RoryA]

Try this:

=GETPIVOTDATA(""&$Z$2,'[Working File.xlsx]BUD2016-Pivot'!$A$5,"CC code",$D$2,"Field1",$C12)

cf here: http://excelmatters.com/2013/05/21/18/

 

[PATSYS]

Try this:

=GETPIVOTDATA(""&$Z$2,'[Working File.xlsx]BUD2016-Pivot'!$A$5,"CC code",$D$2,"Field1",$C12)

cf here: Customising GETPIVOTDATA formulas | Excel Matters

It works, thanks!