GETPIVOTDATA query

  • Thread starter Thread starter Laurence Lombard
  • Start date Start date
L

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
 
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.

Laurence said:
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
Click to expand...
 
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

Debra Dalgleish said:
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.

Laurence said:
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
Click to expand...
Click to expand...
 
Back
Top