2

I have a pivot table with some data that im trying to reference with one sheet and then cross reference it with another to compare the two values.

What I have at the moment is similar tot he formula listed here - http://www.techonthenet.com/excel/pivottbls/getpivotdata2.php

However, instead of writing =GetPivotData (team_count,"'Team 1' 'Count of B'") and then changing it for the rest of the cells in my worksheet.

I want to be able to reference cells that contain the data I want to look up. So for example, I would have Team 1 in cell A2 and Count of B in cell A3.

Ive tried rewriting the formula to =GetPivotData (team_count, A2, A3) but that just gives me an error :/

Keva161
  • 2,623
  • 9
  • 44
  • 68
  • have you tried using INDIRECT around A2 and A3 ? – whytheq Apr 11 '12 at 09:31
  • I assume you mean something like `=GETPIVOTDATA(team_count,INDIRECT(A2,a3))` that just gives me a `#VALUE` error – Keva161 Apr 11 '12 at 10:27
  • When you entered "Team 1" did you put quotes around it? I tried this in Excel 2007 and if I entered "Team 1" without quotes, the formula worked. – David Apr 11 '12 at 10:40
  • seems to be 4 arguments in the GETPIVOTDATA formula I'm playing with _=GETPIVOTDATA("amound",$E$17,H16,H15)_ the second argumnet seems to be an anchor to the top left cell of the pivot table. – whytheq Apr 11 '12 at 11:10

2 Answers2

4

In order to avoid extra quotes, you can also use the T() function:

=GETPIVOTDATA( T(team_count), <Ref address of pivottable>, A2, A3)
gunr2171
  • 16,104
  • 25
  • 61
  • 88
Mark
  • 41
  • 2
  • I needed to use T() for the first argument in the GETPIVOTDATA function, which is the one that says what 'statistic' you're going to get. (I.e.: Sum of product_sales.) – Ward W Dec 03 '14 at 19:39
2

if you want to create a string "'Team 1' 'Count of B'" from cells A2 and A3:

=getpivotdata(team_count, "'" & A2 & "' '" & A3 & "'")

Aprillion
  • 21,510
  • 5
  • 55
  • 89