I've already asked this over on StackOverflow, but realise it may be more appropriate here.

Is it possible to reference multiple fields and items from a single cell into a GETPIVOTDATA formula?

I have a dashboard that displays data from a pivottable based on a number of user controls. Depending on what has been selected, I may need to use a single field and item or multiple to reference the correct data. I am able to build a string of the required fields and items in a cell, but cannot seem to insert it into a GETPIVOTDATA formula. It seems to treat the cell as a single item within inverted commas. Is there anyway around this?

Example code:
=GetPivotData("Time", PTSchedule, $A$1)
Cell $A$1 contains "ID", $A$5, "Team", "Team 1"

Ideally this would evaluate to

=GetPivotData("Time", PTSchedule, "ID", $A$5, "Team", "Team 1")

However, it seems to evaluate to

=GetPivotData("Time", PTSchedule, ""ID", $A$5, "Team", "Team 1"")

Any ideas?

The data I am dealing with has several levels of hierarchy to it: Manager > Team > Agent Depending on the user's choices, I want to pull back data at any one of these levels. This means that the GETPIVOTDATA statement may need between 1 and 3 field/item pairings to identify the correct info.

I suppose I could use three GETPIVOTDATA and wrap them with a CHOOSE function:

=CHOOSE(Selection, GETPIVOTDATA("Sales", PTSALES, "Manager", Manager),
                   GETPIVOTDATA("Sales", PTSALES, "Manager", Manager, "Team", Team),
                   GETPIVOTDATA("Sales", PTSALES, "Manager", Manager, "Team", Team, "Agent", Agent))

However, I was hoping for a neater solution that would move all of the 'variable' values into a single location where they would be easier to maintain, rather than distributed across every cell where I pull data from the pivot table.


The official Excel documentation states the syntax of GETPIVOTDATA is:


However there is an alternative and more flexible way of using the GETPIVOTDATA which is not documented:

GETPIVOTDATA(pivot_table,"'Sum of " & data_field & "' '" & item1 & 
     "' '" & item2 & "' '" & ... & "'")

Where Sum of could be replaced by other aggregation types.

The solution to your problem is therefore:

  "'Sum of Sales' " &
  "'" & Manager & "' " &
  if(len(Team>0),"'" & Team & "' ","") &
  if(len(Agent>0),"'" & Agent & '","")

And make sure that the named range Team and Agent are empty in case you want to have to aggregate over them. For clarity you might want to move the if statements to a separate cell



But since you have "a number of user controls", why not simply construct your GETPIVOTDATA directly from their output eg:

=GETPIVOTDATA("Time", PTSchedule, "ID", $A$5, "Team", $B$7)

So for each control, get the output into its own cell (with some error-checking along the way of course) and use these in your formula.

(Aside: for robustness, I would always use a cell to get the field label from rather than hard coding it, eg reference the original column heading. This way if someone renames "Team" to "Group" or "ID" to "SSN" in the original source table and therefore in the PT, your formula won't break.)

Unfortunately this does not provide a simple way to ignore an empty item such as if IDis not specified, but of course you can wrap the whole thing into an IF statement to check for this:

=IF($A$5="",GETPIVOTDATA("Time", PTSchedule, "Team", $B$7),GETPIVOTDATA("Time", PTSchedule, "ID", $A$5, "Team", $B$7))

This will only work if your PT hierarchy is Team > Id, and Team has subtotals visible (GetPivotData will only return values already calculated and displayed in the PT).

Or simply tell the user there is a problem if they don't fill everything in:

=IFERROR(GETPIVOTDATA("Time", PTSchedule, "ID", $A$5, "Team", $B$7),"Please choose all parameters")
  • Thanks for that. I was hoping for a neater solution, I've added a few more details to the original question in the hope it will help. – Michael Oct 9 '12 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.