Get pivot data

[KatSlif]

Hello
I am using the below formula to source data a from a pivot:

=IFERROR(IF($C2="ROUTINE",GETPIVOTDATA("AllocatedNew",SLOTS!$B$52,"Date",B$6,"Years",B$5),IF($C2="URGENT",GETPIVOTDATA("AllocatedNew",SLOTS!$B$95,"Date",B$6,"Years",B$5),IF($C2="BOTH",GETPIVOTDATA("AllocatedNew",SLOTS!$B$126,"Date",B$6,"Years",B$5),IF($C2="ALL",GETPIVOTDATA("AllocatedNew",SLOTS!$B$6,"Date",B$6,"Years",B$5))))),"")

The beginning on the formula is looking at a drop down filter with 4 options:routine, urgent, both and All and it is bringing through the correct figures.

However, I have another filter where you can select a specialty and this datais available in the pivot, example:



[TABLE="width: 283"]
<tbody>[TR]
[TD="width: 143"] Sum of AllocatedNew
[/TD]
[TD="width: 59"] Years
[/TD]
[TD="width: 59"] Date
[/TD]
[TD="width: 59"][/TD]
[TD="width: 59"][/TD]
[/TR]
[TR]
[TD="width: 143"][/TD]
[TD="width: 59"] 2018
[/TD]
[TD="width: 59"] 2018
[/TD]
[TD="width: 59"] 2018
[/TD]
[TD="width: 59"] 2018
[/TD]
[/TR]
[TR]
[TD="width: 143"] SpecFinal
[/TD]
[TD="width: 59"] Apr
[/TD]
[TD="width: 59"] May
[/TD]
[TD="width: 59"] Jun
[/TD]
[TD="width: 59"] Jul
[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"] ANAE
[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[TD="width: 59, bgcolor: transparent"]

456​

[/TD]
[TD="width: 59, bgcolor: transparent"]

789​

[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"] BREAST
[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[TD="width: 59, bgcolor: transparent"]

456​

[/TD]
[TD="width: 59, bgcolor: transparent"]

789​

[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"] CARD
[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[TD="width: 59, bgcolor: transparent"]

456​

[/TD]
[TD="width: 59, bgcolor: transparent"]

789​

[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[/TR]
</tbody>[/TABLE]

I need the above formula to also look at the speciality dropdown filter but I am not sure where to put this code in the formula? The speciality drop down is found in cell A2

Does this make sense?
Any help appreciated
Katherine

 

[Mathexcel]

Hello
I am using the below formula to source data a from a pivot:

=IFERROR(IF($C2="ROUTINE",GETPIVOTDATA("AllocatedNew",SLOTS!$B$52,"Date",B$6,"Years",B$5),IF($C2="URGENT",GETPIVOTDATA("AllocatedNew",SLOTS!$B$95,"Date",B$6,"Years",B$5),IF($C2="BOTH",GETPIVOTDATA("AllocatedNew",SLOTS!$B$126,"Date",B$6,"Years",B$5),IF($C2="ALL",GETPIVOTDATA("AllocatedNew",SLOTS!$B$6,"Date",B$6,"Years",B$5))))),"")

The beginning on the formula is looking at a drop down filter with 4 options:routine, urgent, both and All and it is bringing through the correct figures.

However, I have another filter where you can select a specialty and this datais available in the pivot, example:



[TABLE="width: 283"]
<tbody>[TR]
[TD="width: 143"]Sum of AllocatedNew[/TD]
[TD="width: 59"]Years[/TD]
[TD="width: 59"]Date[/TD]
[TD="width: 59"][/TD]
[TD="width: 59"][/TD]
[/TR]
[TR]
[TD="width: 143"][/TD]
[TD="width: 59"]2018[/TD]
[TD="width: 59"]2018[/TD]
[TD="width: 59"]2018[/TD]
[TD="width: 59"]2018[/TD]
[/TR]
[TR]
[TD="width: 143"]SpecFinal[/TD]
[TD="width: 59"]Apr[/TD]
[TD="width: 59"]May[/TD]
[TD="width: 59"]Jun[/TD]
[TD="width: 59"]Jul[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"]ANAE[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[TD="width: 59, bgcolor: transparent"]

456​

[/TD]
[TD="width: 59, bgcolor: transparent"]

789​

[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"]BREAST[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[TD="width: 59, bgcolor: transparent"]

456​

[/TD]
[TD="width: 59, bgcolor: transparent"]

789​

[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[/TR]
[TR]
[TD="width: 143, bgcolor: transparent"]CARD[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[TD="width: 59, bgcolor: transparent"]

456​

[/TD]
[TD="width: 59, bgcolor: transparent"]

789​

[/TD]
[TD="width: 59, bgcolor: transparent"]

123​

[/TD]
[/TR]
</tbody>[/TABLE]

I need the above formula to also look at the speciality dropdown filter but I am not sure where to put this code in the formula? The speciality drop down is found in cell A2

Does this make sense?
Any help appreciated
Katherine

Hi Katherine,

It isn't clear as to what you want to achieve with the second dropdown.
Could you please give a better explaination as to what is the desired situation?
Both dropdown are linked to the same output? You fetch your data from the same pivot? Please give more information so I can provide you with a solution.

Thx,
Matt