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.