Forum Discussion
Conditional Formatting Help
I am a department supervisor at a large medical center. Every morning, I create a spreadsheet that details where each of my employees will be at each hour of the day, as they rotate post locations hourly. I have 6 locations that must be filled at every hour, with two additional optional locations if I have enough people to fill them (see example). I am human, so at times I miss a location in one hour block and I have to scramble to fill it on the spot.
What I would like to do is create a formula to run a check at each hour block, and if each mandatory location is filled, it will fill a green check mark in a box outside of the print area on the line that it checked. If there is a location missing, I would like it to display a yellow exclamation point to quickly show that a mandatory location has not been assigned.
How might I be able to go about creating this? TLDR; Run a check on 8 boxes in a table to show if all 6 mandatory locations are filled.
So, mandatory locations are defined somewhere in Excel
let say in L1:L6. Actually it could be in another hided sheet, doesn't matter. Let add in J3 formula
=SUM(COUNTIF($L$1:$L$6,B$3:I$3))and drag it down till end of the range. It returns 6 if all mandatory locations are listed in the row, less number otherwise.
After that apply conditional formatting with icons set to our cells in column J
In rule description
select Type Number, set 6 for first value and zero for next one; select from drop down list desired icons for first two; and select Show Icon Only.
5 Replies
How do we know which locations are mandatory and which are not?
SergeiBaklan Sorry about that, the mandatory locations are listed as P3, ED 1, ED 3, E POD, NB 1, and NB 2. The ones listed as RV or C7 are optional.
So, mandatory locations are defined somewhere in Excel
let say in L1:L6. Actually it could be in another hided sheet, doesn't matter. Let add in J3 formula
=SUM(COUNTIF($L$1:$L$6,B$3:I$3))and drag it down till end of the range. It returns 6 if all mandatory locations are listed in the row, less number otherwise.
After that apply conditional formatting with icons set to our cells in column J
In rule description
select Type Number, set 6 for first value and zero for next one; select from drop down list desired icons for first two; and select Show Icon Only.
