GETPIVOTDATA query

[Laurence Lombard]

The formula below works. It gets the VAT value from the pivot table for TP =
1.
GETPIVOTDATA($B$5,"TP['1'] VAT")


Is there a way that I can reference the TP outside the formula - something
like
GETPIVOTDATA($B$5,"TP[' & A1 & '] VAT") so that I can choose which TP to
see by changing the value in A1.

Thanks
Laurence Lombard

 

[Debra Dalgleish]

Add some quotation marks, and it should work:

=GETPIVOTDATA($B$8,"TP " & A1 & " VAT")

It should work without the square brackets and single quotes around the
number.

The formula below works. It gets the VAT value from the pivot table for TP =
1.
GETPIVOTDATA($B$5,"TP['1'] VAT")


Is there a way that I can reference the TP outside the formula - something
like
GETPIVOTDATA($B$5,"TP[' & A1 & '] VAT") so that I can choose which TP to
see by changing the value in A1.

Thanks
Laurence Lombard

 

[Laurence Lombard]

Thanks Debra for your prompt reply. What works in the end is

=GETPIVOTDATA($B$8,"TP['" & A1 & "'] VAT") so that the result reads

=GETPIVOTDATA($B$8,"TP['1'] VAT")

The '1' must end up in single quotation marks and in square brackets. It
took a while to works that one out. In this case the '1' is the name of an
actual item in the pivot table, but according to the help file one can use
it to reference the 1st, 2nd etc items. The syntax remains the same.

Laurence

Add some quotation marks, and it should work:

=GETPIVOTDATA($B$8,"TP " & A1 & " VAT")

It should work without the square brackets and single quotes around the
number.

The formula below works. It gets the VAT value from the pivot table for TP =
1.
GETPIVOTDATA($B$5,"TP['1'] VAT")


Is there a way that I can reference the TP outside the formula - something
like
GETPIVOTDATA($B$5,"TP[' & A1 & '] VAT") so that I can choose which TP to
see by changing the value in A1.

Thanks
Laurence Lombard