Hello everyone ~ I'm using excel to track provider certifications and need some assistance in formatting a visual cue over a lot of data.
Background:
I use excel to track paramedics to see that they have 4 hours of medical control per year throughout their 3-year certification process. This would be easy to do if everyone were on the same annual cycle, but they are not. Provider certification cycles vary by month throughout the three years based on when they initially certified. I have about 100 paramedics to follow, each on their own different recertification cycle. It is difficult to see what year an individual is in, whether they have met their required hours, or how many hours they have left to do.
I use a single summary sheet to track providers, and each has their own separate sheet that feeds the summary sheet. I would love to see the current year a paramedic is in as a highlighted cell, based on (today’s date). That way I could see what year they are in, and if they met the necessary hours for this year.
Attached is an example, where I have used conditional formatting to cue how soon until a provider must recertify, and text color formatting based on the value in the cell. However, I cannot figure out how to highlight a cell in the MD column based on what year of the cycle they are actually in.
For example, I would like to highlight E3 to show that Smith is in his third year, D4 to show that Adams is in his second year, C7 to show that Cook is in his first year, (based on today’s date) etc... I hope I am explaining this well enough!
Thank you much!
David
CME File Example.xlsx
Bookmarks