Hello all!
I've learned recently that I know nothing about the capabilities of excel. I've managed to get this far alone, but I'm stuck and hope you lovely folks will be able to point me in the right direction.
I recently offered to help my SO make an easier excel file to track vacation days of his colleagues (the one the company offers was made back in 1995 and is horrendous. They plan on updating it never.) The criteria for this project is to have two worksheets:
1. A pivot table of the yearly calendar (calendar.jpg and pivot.jpg below)
2. Data used for the pivot table. (data.jpg below)
For each day of the month, a max of only 2 people are allowed to take a day off. In an effort to make it easier for his colleagues to determine when they can take time off, my SO would like the days in the calendar to remain "white" if there are two slots available, "yellow" if only one slot is available, and "red" if no slots are available for vacation.
In my data sheet, he will list their names in "First slot" and "second slot" on the days they select. I have made it that depending on the status of each cell, a number will appear in the corresponding columns "slot 1" and "slot 2", where if blank, the value is "0", if there is text, the value is "1." I totaled these in the "Total" column.
I am not sure if I'm going the wrong direction, but my thought process is that if the result for a specific date in the "Total" column was "0", then the date would remain "white", if "1", it would be "yellow" and if "2" then "red", meaning no one else can take that day off... but I have no clue how to do that.
Here is what I have right now:
calendar.JPG
pivot.JPG
data.JPG
Any help or suggestions would be greatly appreciated.
ETA: I just realized, I could have just uploaded the excel file (DOH!): Attachment 280561
Bookmarks