Conditional Formatting with Matches

[mark.k.conroy]

Hey all, I thought I solved this but it appears I didn't. So I'm creating a new, more focused post. On the attached spreadsheet, managers track absences from work by filling out data for each absence. Supervisors have requested a color coated "calendar" for visual representation. What I'm trying to do is for any cells B70:I122 conditionally format them based on 4 conditions. If the date in B70:I122 is listed in column M - then i need to match the following, to update the condition formatting the cell in B70:I122 based on the date.

Q# call = yes, then change font to red
R# cell = No, then change fill to red
R# cell = Yes, then change fill to green
S# cell = yes, then change fill to gray

Someone can have red font in any of the other conditions.
However, gray fill trumps red or green fill

The table in M2:S134 already has this all set up - but the superiors would like something they can give to their staff to show trends so if hte date matches, the formatting should batch table M2:S134.

Thoughts?

[Keebellah]

The thing is you should watch out when selecting areas and setting up conditional format rules.
You had about over 60 rules set.
You only need 4 and place them in the correct order.
I hope this is what you're looking for, else you can odify it easily since the number of rules has been reduced to the normal level.
Always select the entire area when setting ruls and see where it applies to to no loose track

[mark.k.conroy]

Thanks for that update - I was struggling to figure out how to make that work! Much appreciated on streamlining that. Any thoughts on the conditional formatting for the chart in the lower left of the sheet?

[Keebellah]

I think with the VLOOPKUP you're using it'll work but you could make it a little easier for yourself.
You could insert a helper column in between S and T so T moves up one.
In this column you place a formula that defaults to 0 and then think up values for the different combinations of Q, R and S
Q=Yes = 1
R= No = 2
R = Yes = 3
S = Yes = 4

hide this column but use it for the coloring if 1 the font is RED
if 2 the fill for R=No
if 3 the fil etc.

You can then even use it for the previous formatting and simplify if.
Hope you i've been able to explain it.
I'm off to bed, it's past midnight now

[Keebellah]

I don't think the extra column will help.
I did try with the table below but for some reason the R Yes and R No conditions don't apply
You'll have to play around with the conditional format of that block, maybe the order in which they're processed, can also be the date format on my system

[mark.k.conroy]

Here's what I ended up going with - I inserted 6 columns for my conditions to match to. MUCH simpler lol. SO if someone has leave usage on 04/15/19 then that date will appear in the correct column. Then the conditions have this formula: =MATCH(B68,$T$2:$T$132,0) where I just change the column/row that I'm matching to for the date needed....attached for reference. Thanks for your help!

[Keebellah]

Don't mention it,good to know you got it working for you