I've hit a bit of a roadblock with this spreadsheet I'm working on. I'm trying to create a visual
schedule matrix for my office which gives a visual representation of who's on and who's off (whether due to break, lunch, or
not on shift) to hand out to the supervisors and managers in my call center. I've gotten the basic matrix worked out (with
some extensive formulas) with simple coloration. The issue I'm running into is differentiating different types of reps by
what their job is. Most reps we have are phone reps, however we also have an email address that people write in to with
questions. I'd like to separate the email reps by showing them as a different color. Here's a quick synoposis of what it
Rep Name | Rep Type (Phone or Email) | Individual 15-minute blocks which, due to an embedded formula,
displays either ON or OFF when it checks what time-block it falls under vs the employee's shift. (Such as, if between Shift
Start Time and Break 1 Start Time, it'll display ON, else it'll display OFF.)
I want it to show that anyone that's
listed as Phone has their ON color as yellow, and anyone that's listed as Email has their ON color as a light purple (Colors
are arbitrary, I know!)
Anyway this is what I thought would do it. I band-selected the entire Time portion of the
matrix, sans headers, and added a conditional format based on a formula. This is the formula I used:
Where D4:D23 are the role designations for the
reps (Phone or Email) and E4:BA23 is the visual matrix (With each cell containing ON or OFF based on the rep's schedule.)
The Affected Area for this conditional format is the same, E4:BA23.
Nothing happened when I tried that. I
understand that it's likely because it's coming up with a FALSE due to how it calculates it. I was expecting it to calculate
the formula in each individual square, checking to see if, say in cell R12, if it contained ON, and then cell D12 contained
Email, it would return TRUE, and therefore color the cell as I had planned it. This calculation would repeat across every
cell in the affected area... well it did not do that or I wouldn't be posting here.
Anyway, what could I do to
fix this? I would rather not have to resort to just doing a new basic conditional format for the affected rows, because I
want to turn this into an automated tool that the managers and supervisors can use without having to delve deep into this
spreadsheet's gizzards. I've attached a screenshot of this matrix to help explain the above a little better than what I can
do with simple text.
IMPORTANT EDIT: Dummy Worksheet attached on my reply below, please use that instead of the
JPG attached to this original post.