I am trying to set up a complex (for me anyway) series of conditional formatting options for my spreadsheet. I am scheduling music leaders for each weekend at my church. Allow me to explain what I am trying to do...
- Cell starts grey, which tells me that I have not worked on this week/leader yet.
- "1" activates a check mark icon, telling me that the leader has been scheduled
- "0" turns the cell white, meaning that they have not been scheduled this week.
- "2" turns the cell black, indicating that the leader has blocked out this week, and is unable to lead.
Here's the tricky part, where I need your help.
- If more than one worship leader is leading at the same time, I would like the entire column to turn red EXCEPT the black blockout dates. I still need to see those that are blocked out.
I have formatting that will turn the entire column red, but I can't seem to work it out so that blocked out columns remain black.
I have a feeling that I either have them in the wrong order, or I need to build all of this into one conditional formula. I just don't know how.
Cell Color Change Conditional Formatting
Cell Color Formatting.png
Checkmark Conditional Formatting
Check Mark Formatting.jpg
Conditional Formatting Order
Conditional Formatting Order.jpg
Red Conditional Formatting ISSUE
- B3 still needs to show BLACK, as there is a "2" in the cell which tells the Cell Color Formatting to change the cell black. The problem is that the Red Conditioning Overrides the black formatting. I need B3 to stay black, but B4:B8 to change to red. Any black cells in B3:B8 need to stay black.
Red Formatting .jpg
WORKSHEET
Worship Leader Scheduling-2013.xlsx
Please let me know if you have a solution. Much appreciated!
Keith
Bookmarks