Hi everyone
Firstly, what an amazing resource this place is! It's my first post here and I am hoping that someone can help 🙂 I apologise in advance for how long winded this is but I thought it best to fully describe my methodology in order for you to understand what it is that I am trying to achieve!
Sp I have built a spreadsheet that analyses staff utilisation over a period of time. I am specifically looking at percentage of time spent on chargeable vs admin vs leave, whilst also identifying where staff members are working too many or too little hours. I need to show the % split by employee and by teams. My data set is timesheet entries exported from our database.
Firstly I created an extra column in my data set being “Utilisation value”. This utilisation value takes the quantity of time entered divided by the number of hours available in the period. For example, if we are looking at utilisation over a standard 40 hour week and the employee has entered 25 hours of chargeable time, the chargeable utilisation value would therefore be 25 / 40 = 62.5%.
In order to properly analyse the data I needed to create a multiplier per employee that takes into account whether said person works FT vs PT hours, and also considers where people start or leave within set time period. Taking the same example as above that this employee’s contract is actually 32 hours per week. Therefore a multiplier of 1.25 (40 / 32) is applied and the chargeable utilisation therefore becomes ~ 78%.
Each employee reports into a Team Leader (TL), who in turn reports into a Head of Department (HD) with all heads of department reporting into the CEO. I have created three additional columns on my data set that identifies which TL employee reports into, which HD the TL reports into and where the HD reports into the CEO. By doing this the TL’s data will appear both within their own team and also within the HD’s team.
I have built a pivot table and chart with three different slicers:
- TL selection (list of names)
- HD selection (list of names)
- CEO selection (“Yes” or “No”)
The catch is, I need to always clear the filters on all slicers when selecting different teams. I have no problem with doing myself however it's gets a bit confusing for the end user who may or may not be an experienced Excel user.
In the ideal world, there would either just be one slicer in which you could select names for all three slicers (which would obviously take into account where one person sits within multiple teams). Failing that, is there a way to automate the clear filter function when you select different slicer names? I’d prefer to avoid VBA but have no issue if this is the only solution.
Massive thanks in advance for any suggestions!
Euan
Hi Euan,
Thanks for taking the time to clearly describe your scenario. It made it easy to follow.
Unfortunately there's no way to automatically clear other Slicers without resorting to VBA. An alternative is to make 3 versions of the report; one with the TL Slicer, another with the HD Slicer and the third one with the CEO Slicer. That way there is only one Slicer to filter per report and you don't need to clear the other Slicers.
Hope that makes sense.
Mynda
Euan,
Without seeing the actual structure of your data, I made a quick slicer example loosely based on a table of data containing Employee, Team Leader (TL), and Department Head (DH). The slicers in this example are based on the table data, you could also create three separate Pivot Tables containing each of these and then create the slicers from the Pivot Tables.
When you select a TL, it only shows the DH and employee tied to each of these. This is accomplished by hiding items with no data in the slicer settings. If clearing the filters manually is not an option, I also included a Clear Filters button with a simple macro. Don't know if this is what you are looking for but maybe it will help guide you in the direction you want.
Doug Mace
