VBA - whats the best way to filter this pivot

[mahmed1]

Hi All

I have 3 pivot tables that has the department name in the filter section, team names in the row section and scores in the values section

I have created a slicer on the department name. Now here is my problem

Each department has different number of teams.

What i want to do is when i select a department, in the row field sort the teams in A-Z and filter only the first 3 teams (Some department may only have 2 teams but the max i want to filter is to the first 3 teams sorted in A-Z - If a user tries to select multiple departments from the slicer then i need a message box saying you can only select 1 department at a time (so disable the multiple selection option)

So Pivot1 is filtered to the 1st Team
Pivot 2 to the second team
Pivot3 to the 3rd team

I am using excel 2013 and my pivot tableS are called Pivot1, Pivot2 and Pivot3 - the slicer for the department is called Dept

I hope this is enough info

for eg

Raw Data

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Team[/TD]
[TD="width: 64"]Score[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Team1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Team2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Team3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Team4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Team5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Team5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Team7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Team7[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Team7[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

Slicer list

A
B
C

When A is selected then

1st pivot to show

[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]Team1[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[/TR]
</tbody>[/TABLE]

2nd Pivot

[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]Team2[/TD]
[/TR]
[TR]
[TD]Grand Total

3rd Pivot

[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]Team3[/TD]
[/TR]
[TR]
[TD]Grand Total

If B is selected then

1st pivot

[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]Team4[/TD]
[/TR]
[TR]
[TD]Grand Total

2nd pivot

[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]Team5[/TD]
[/TR]
[TR]
[TD]Grand Total

3rd Pivot is blank

[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]Grand Total

I hope this makes sense[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[cogswel__cogs]

Are all three pivots using the same data source?
If so you can attach all three pivots to the slicer will that get you what you want?

 

[mahmed1]

Hi
Yes all 3 pivots using same data source however when i change the department- the team filter doesn’t change so that wont work

thanks

 

[cogswel__cogs]

You can link the slicer to all three pivots.
Right click on the pivot and click on report connections.
If they are all using the same data range(exact same) there should be a box to connect each.

 

[mahmed1]

Hi

the slicer is connected to all pivots - however if i select a different department- the filter (filter within the rows of the pivot) needs to be filtered to the 1st item and same for other pivots

i hope im explaining myself properly- apologies if i aint

 

[cogswel__cogs]

Really sorry just not visualizing what you are trying to do.
Maybe someone else can get a better handle on it.