Hi, I'm new to the forum and I'm looking for some help on a spreadsheet I'm working on, I've spent a good number of hours trying to figure out how to achieve this using VBA(which I am new to).
It is a training matrix and I would like to filter out training dates that are out of date/obsolete or require refresher training. Secondary, I would like to also be able to filter out columns and be able to apply the filter for out of date/obsolete or refresher training due specific for that column.
A sample of the spreadsheet is at the bottom of this post.
I've attempted to make the filtering of training status by adding columns DE and DF which count the dates as appropriate.
In cell D2 is a drop-down that has a list of "All, Refresh & Obsolete". The desired outcome will be:
All = Unhide rows (7 - 150)
Refresh = Hide all rows (7 - 150) that do not contain a date that is counted as a refresher (conditionally formatted as yellow) - this will determined by the cell in DF being 0
Obsolete = Hide all rows (7 - 150)that do not contain a date that is counted as a retrain (conditionally formatted as red) - this will determined by the cell in DE being 0
The second objective is to add a second filter that will allow a name to be selected in cell G2 (data validated from headers in table, columns I - BF) and then filter out the training status, the problem with this is that you won't be able to use the counts in columns DE and DF because this is a count of everybody's training. So this may not be possible, but the below may be useful for figuring this out?
The conditional formatting turns the cells yellow based off another cell which is smaller than today(). The formula for that cell is =IFERROR(IF(I9="","",VLOOKUP($E9,'.'!$C$2:$D$10,2,FALSE)+I9),"") - I have a tab called "." that contains a table to account for leap years and adds this to the date in the cell to get the date for refresher training.
The conditional formatting that turns the cells red is simpler, it turns it red if the date in the cell is smaller than the date in column D.
I may attempt to add in another filter later where I can view all the green training... but if the above is solved I shouldn't have any trouble adding that in.
I appreciate any help and advice anyone can give on this.
Edit - Sample Book1 was based off uncomplete version, I have updated this now:
Book1.xlsm
Bookmarks