Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I need to reference some data from a pivot table.
Common data point.
Any way to combine output of a pivot table
Name - Tom
- Dick
- Harry
So that Tom, Dick and Harry are in a single cell which can be referenced?
Thanks!
Not sure what you're trying to do here. You can group rows in a pivot table.
Concatenate?
=GETPIVOTDATA("Values2",$E$2,"Names","Dick") + GETPIVOTDATA("Values2",$E$2,"Names","Harry") etc.
Don't think you can use the names in an array.
If you've got >3 names you need to aggregate you're probly best off using another field e.g. Boys
I think what robhilton said although I tend to have the names tom,dick, harry in one column,the formula just to the right and picking up the names from the left and then sum the whole thing down to the single cellyou are looking for. I find I get less errors that was as it is clearer and easier to check
That is off course assuming that I understand correctly what you are trying to do 🙂
Just happened to be doing something similar to this and realised you can use arrays:
=SUM(GETPIVOTDATA("Values2",$E$1,"Names",{"Dick","Harry","Tom"}))
or
{=SUM(GETPIVOTDATA("Values2",$E$1,"Names",$G2:$G4))}
You just have to get the syntax right 🙂
Oh. Adds to bag of tricks, that is very nice, especially the second notation. I often use named ranges for that stuff and highlight the ranges to make it cleared to others what is going on