Dear Excel Gurus
Thank you for your collective wisdom.
I have a large table of team members from several sites that undertake mandatory training. Currently it lists names in descending rows against their courses across the top. The values against the names and courses are dates that they took the training. Calculated fields to the right of course work out when the dates expire. I have used the expertise given in this forum to conditionally format them according to how close the expiry date is approaching, using red, amber green for expired, within 30 days and greater than 30 days. Some courses last for 6 months, others for example may expire after 2 years.
What I trying to do is to add a slicer to filter out the dates according to the proximity of the expiry date so I can print out just the reds for example. In addition I could filter it by team, then by expiry. My thoughts are that it would be a conditionally formatted pivot table but I cannot seem to get the course title across the columns, only the dates of the courses taken. I have tried to demonstrate what I have in the table below.
How my pivot chart is looking is like this:
It is picking out the minimum date using the field value MIN in the pivot table. Across many personnel it would be ideal to filter by course to find out how many have expired etc, then by team if possible
In my table there are 100 rows of names for 10 different teams across 6 location all with very differing expiry dates. In essence I am trying to add a slicer or two on my spreadsheet that will filter rather than rely on the filter function of excel as each site will be responsible for data entry, with varying skillsets. Therefore, to make it simple to use I would like a slicer for team, one for course selection and one for expiry date please. So far I have filtered the pivot table by the expiry date but I have not fathomed how to link the varying course data in the table. My thoughts are that it should be part of a data model but currently I am stuck so I would greatly appreciate your thoughts and direction. I hope this makes sense to you.
Warmest thanks
Thank you for your collective wisdom.
I have a large table of team members from several sites that undertake mandatory training. Currently it lists names in descending rows against their courses across the top. The values against the names and courses are dates that they took the training. Calculated fields to the right of course work out when the dates expire. I have used the expertise given in this forum to conditionally format them according to how close the expiry date is approaching, using red, amber green for expired, within 30 days and greater than 30 days. Some courses last for 6 months, others for example may expire after 2 years.
What I trying to do is to add a slicer to filter out the dates according to the proximity of the expiry date so I can print out just the reds for example. In addition I could filter it by team, then by expiry. My thoughts are that it would be a conditionally formatted pivot table but I cannot seem to get the course title across the columns, only the dates of the courses taken. I have tried to demonstrate what I have in the table below.
| Name | Team | First Aid | Expiry date - calc field | Manual Handling | Expiry - calc field | Security training | Expiry date - calc field |
| Bloggs | Warehouse | 12/3/20 | 11/9/21 | 12/3/22 | 11/3/21 | 12/3/20 | 11/3/22 |
| Jones | Shop | 18/4/20 | 17/10/20 | 25/9/20 | 24/9/21 | 06/2/20 | 05/2/22 |
| Smith | Management | 12/12/19 | 11/6/20 | 15/1/20 | 14/1/21 | 18/2/20 | 17/2/21 |
How my pivot chart is looking is like this:
| Surname | Jan | Feb | Mar | Apr | Jun | Jul | Aug | Sep | Oct |
| Bloggs | 11/3/21 | ||||||||
| Jones | 17/10/20 | ||||||||
| Smith | 11/6/20 |
It is picking out the minimum date using the field value MIN in the pivot table. Across many personnel it would be ideal to filter by course to find out how many have expired etc, then by team if possible
In my table there are 100 rows of names for 10 different teams across 6 location all with very differing expiry dates. In essence I am trying to add a slicer or two on my spreadsheet that will filter rather than rely on the filter function of excel as each site will be responsible for data entry, with varying skillsets. Therefore, to make it simple to use I would like a slicer for team, one for course selection and one for expiry date please. So far I have filtered the pivot table by the expiry date but I have not fathomed how to link the varying course data in the table. My thoughts are that it should be part of a data model but currently I am stuck so I would greatly appreciate your thoughts and direction. I hope this makes sense to you.
Warmest thanks
