SOLVED

Conditional formatting if multiple cells have an 'x' in them

Copper Contributor

I do a lot of manual scheduling. I want to alert myself that I've scheduled somebody twice in one week. 2 X's turns the student's name red so I know I messed up (e.g. John Smith cell turns red). Is there a formula I can use for this?

 

StudentMONDAYTUESDAY WEDNESDAYTHURSDAYFRIDAY
John Smith x x 
7 Replies

@shrimpusa Use Conditional Formatting with the formula =COUNTA(B2:F2)>1

 

Doug_Robbins_Word_MVP_0-1647319797537.png

 

 

Thanks!@Doug_Robbins_Word_MVP I had to change the >1 ti >2, but this was just what I needed. 

That would not result in the cell being shaded Red until a student was scheduled on 3 days whereas your original requirement was to detect if a student had been scheduled twice.
You're right, I just discovered that, thanks.

There's one more wrinkle. I have additional info in other cells that seems to be a problem with the COUNTA. I've set up a conditional formula so that anytime there's an X in D, F, H or J, the name turns green. But I want to set up an additional formula for those same cells so that if I do 2 X's (schedule somebody for 2 days by accident) the name changes red.

 

Screen Shot 2022-03-15 at 8.49.00 pm.pngScreen Shot 2022-03-15 at 8.51.06 pm.pngScreen Shot 2022-03-15 at 8.53.45 pm.png

best response confirmed by VI_Migration (Silver Contributor)
Solution

@shrimpusa Why not use

=COUNTIF(D3:J3,"x")>1

as the CF rule? 

Riny_van_Eekelen_0-1647339561026.png

 

 

1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@shrimpusa Why not use

=COUNTIF(D3:J3,"x")>1

as the CF rule? 

Riny_van_Eekelen_0-1647339561026.png

 

 

View solution in original post