mahmed1
Well-known Member
- Joined
- Mar 28, 2009
- Messages
- 2,302
- Office Version
- 365
- 2016
- Platform
- 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
<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
<colgroup><col></colgroup><tbody>
</tbody>
2nd Pivot
<colgroup><col></colgroup><tbody>
</tbody>
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
Name | Team | Score |
A | Team1 | 1 |
A | Team2 | 2 |
A | Team3 | 3 |
B | Team4 | 4 |
B | Team5 | 5 |
B | Team5 | 6 |
C | Team7 | 7 |
C | Team7 | 5 |
C | Team7 | 7 |
<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
<colgroup><col></colgroup><tbody> </tbody> |
<colgroup><col></colgroup><tbody>
</tbody>