Referencing multiple fields and items from a single cell - GETPIVOTDATA

The official Excel documentation states the syntax of GETPIVOTDATA is:

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

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:

GETPIVOTDATA(
  PTSALES,
  "'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

No.

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")

Referencing multiple fields and items from a single cell - GETPIVOTDATA

Related

Recent Posts