VBA - whats the best way to filter this pivot

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,302
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

NameTeamScore
ATeam11
ATeam22
ATeam33
BTeam44
BTeam55
BTeam56
CTeam77
CTeam75
CTeam77

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Slicer list

A
B
C

When A is selected then

1st pivot to show

Name
Row Labels
Team1
Grand Total

<colgroup><col></colgroup><tbody>
</tbody>

2nd Pivot

Name
Row Labels
Team2
Grand Total

3rd Pivot

Name
Row Labels
Team3
Grand Total

If B is selected then

1st pivot

Name
Row Labels
Team4
Grand Total

2nd pivot

Name
Row Labels
Team5
Grand Total

3rd Pivot is blank

Name
Row Labels
Grand Total

I hope this makes sense

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Really sorry just not visualizing what you are trying to do.
Maybe someone else can get a better handle on it.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top