Conditional format based on text in same row [Solved/Closed]

Report
Posts
2
Registration date
Tuesday June 20, 2017
Status
Member
Last seen
June 20, 2017
-
Mazzaropi
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
-
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!



4 replies

Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
131
joshcamp, Good afternoon.

I'm glad I could help.

Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
131
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
Posts
2
Registration date
Tuesday June 20, 2017
Status
Member
Last seen
June 20, 2017

Thank you Marcílio, this worked perfectly!