Report

Conditional format based on text in same row [Solved]

Ask a question joshcamp 2Posts Tuesday June 20, 2017Registration date June 20, 2017 Last seen - Last answered on Jun 20, 2017 at 01:29 PM by Mazzaropi
Hi all,

New to this forum.

I'm running Excel 2016 on Win7 -- my spreadsheet has results of inspections in columns E, G, and J. The possible values are Pass, Fail, and Inconclusive. I have these columns conditional-formatted so "Pass" automatically shades green, "Fail" red, and "Inconclusive" yellow.

I would like to have column C automatically change to show the "status" of each row as follows: if E, G, or J in the same row show Pass, C would shade green and automatically add the text "Pass". If there is no "Pass" in any of these columns, C would shade red and add the text "Fail". Unlike the other columns, I would like to not have to manually update this column.

I'm not sure if this would be approached as a conditional format, a simple IF function, or both, and it's left me scratching my head.

Thank you very much for your help!



See more 
Helpful
+1
plus moins
joshcamp, good morning.

"...I'm not sure if this would be approached as a conditional format, a simple IF function, or both, ..."
You'll need to use BOTH.

Try to use:

a) C1 --> =IF(OR(E1="Pass", G1="Pass", J1="Pass"), "Pass", IF(AND(E1<>"Pass", G1<>"Pass", J1<>"Pass"), "Fail", "" ))

b) Conditional Format
Select C column as desired
Using formula

Rule 1
--> =$C1="Pass"
--> Format: Fill color GREEN

Rule 2
--> =$C1="Fail"
--> Format: Fill color RED
OK

Please, tell us if it worked as you desired.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
joshcamp 2Posts Tuesday June 20, 2017Registration date June 20, 2017 Last seen - Jun 20, 2017 at 12:26 PM
Thank you Marcílio, this worked perfectly!
Reply
Leave a comment
Helpful
+0
plus moins
joshcamp, Good afternoon.

I'm glad I could help.

Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!