borntorun75
Board Regular
- Joined
- Jul 12, 2010
- Messages
- 57
Hi there,
I'm new to using slicers and really like the functionality they bring to a pivot.
In the example below, I'm showing the source data (A1 to E17) and a couple of pivots. You'll see that for each department (Sales and Admin), there's a Country and Area - with Wages information for each. In respect of the Area (column C), in terms of relationships its 'parent' is the Country (column B)
So, Country USA has 4 areas (North, East, South and West). Country Ireland has one area called (all)
The first pivot (G1 to H16) is a traditional pivot (no slicers).
The second pivot (J17 to M22) is a pivot with the 3 slicers you see positioned above it.
As you can see from the screen shot, on the slicers I've currently selected Sales and Ireland. The 3rd slicer correctly shows that the only Area available to select is (all). That's highlighted in the darker blue, at the top of the list.
This third slicer also shows the Areas of the other available Countries below it (North, East, South and West). As you can see from the data, they don't apply to the selected country of Ireland in this case.
My question is can I control the contents of the 3rd slicer box to only show the Area(s) of the parent Country (in the 2nd slicer box).
Can I show that relationship and only populate the contents of child slicers based on the children its parent has ... ?
In this example, since I've selected Ireland, could the 3rd slicer box be manipulated so that it only showed "(all)"? Similarly if I selected USA as the Country, could the 3rd slicer box be manipulated so that it only showed the 4 Areas (North, East, South and West) ?
I can get by with how it is now, but I'm working on something that I want to distribute and i think it'd be a nicer solution to not show children items that don't belong to the parent.
Many thanks. I'm using Excel 2010 on Windows 7.
My usual preference is to try and achieve results by avoiding VBA, so that users don't have to 'manage' turning on macros, etc. If there's a non-VBA solution, that'd be great. However, I appreciate that sometimes VBA is the only way to resolve a problem easily.
Best regards,
Mike.
I'm new to using slicers and really like the functionality they bring to a pivot.
In the example below, I'm showing the source data (A1 to E17) and a couple of pivots. You'll see that for each department (Sales and Admin), there's a Country and Area - with Wages information for each. In respect of the Area (column C), in terms of relationships its 'parent' is the Country (column B)
So, Country USA has 4 areas (North, East, South and West). Country Ireland has one area called (all)
The first pivot (G1 to H16) is a traditional pivot (no slicers).
The second pivot (J17 to M22) is a pivot with the 3 slicers you see positioned above it.
As you can see from the screen shot, on the slicers I've currently selected Sales and Ireland. The 3rd slicer correctly shows that the only Area available to select is (all). That's highlighted in the darker blue, at the top of the list.
This third slicer also shows the Areas of the other available Countries below it (North, East, South and West). As you can see from the data, they don't apply to the selected country of Ireland in this case.
My question is can I control the contents of the 3rd slicer box to only show the Area(s) of the parent Country (in the 2nd slicer box).
Can I show that relationship and only populate the contents of child slicers based on the children its parent has ... ?
In this example, since I've selected Ireland, could the 3rd slicer box be manipulated so that it only showed "(all)"? Similarly if I selected USA as the Country, could the 3rd slicer box be manipulated so that it only showed the 4 Areas (North, East, South and West) ?
I can get by with how it is now, but I'm working on something that I want to distribute and i think it'd be a nicer solution to not show children items that don't belong to the parent.
Many thanks. I'm using Excel 2010 on Windows 7.
My usual preference is to try and achieve results by avoiding VBA, so that users don't have to 'manage' turning on macros, etc. If there's a non-VBA solution, that'd be great. However, I appreciate that sometimes VBA is the only way to resolve a problem easily.
Best regards,
Mike.
