Jun 27 2018 08:19 AM - edited Jun 27 2018 10:49 AM
Hi!
I can't find a way to make this conditional formatting work how I need. The columns that I am working with are A, E and F. I need to set up conditional formatting for column A.
ID (A) | Prefix (B) | FirstName (C) | LastName (D) | Phone (E) | Complete (F) |
63077 | Mrs. | First | Last | Phone | x |
171015 | Mrs. | First | Last | ||
26473 | Mrs. | First | Last | x | |
6685 | Mr & Mrs | First | Last | Phone | x |
127225 | First | Last | Phone | x |
The formatting should be as follows:
If E is blank/empty and F is blank/empty, A is filled Yellow.
If E is blank/empty and F has "x" value, Stop Rule.
Can someone help me figure out how to do this?
Thanks!
Jun 27 2018 09:47 AM
Hi Carly,
On you sample, if first column is A, you have columns from A to F. What is G?
Jun 27 2018 10:35 AM
Jun 27 2018 10:55 AM
Okay, thank you.
You may apply the rule
=ISBLANK($E2)*ISBLANK($F2)*NOT(ISBLANK($A2))
to your column A to fill it yellow if both E and F are blank, no need in Stop rules. As attached
Jun 27 2018 11:26 AM
Jun 27 2018 11:32 AM
Jun 27 2018 11:35 AM
Could you please check the file I attached in previous post to be sure the formula is applied correctly
Jun 27 2018 11:40 AM
Here is a copy of my document.
Prefix | FirstName | WorkPhone | HomePhone | Letter | |
116251 | Mr & Mrs | Gary and Laura | (905) 692 | ||
113412 | Mr & Mrs | Peter & Hilda | 204-857 | ||
126599 | Ms. | Gilbert & Joyce | (519) 449 | ||
107389 | Mr & Mrs | Pierre & Gisele | (613) 938 | ||
152918 | (250) 893 | x | |||
170255 | Ms. | Audrey | (705) 435 | ||
126643 | Mr. | Richard | 306-889 | 306-889 | |
14046 | Mrs. | Edna | (519) 925 | ||
171494 | Mr & Mrs | Hendrik & Janice | (250) 846 | ||
168317 | Mrs. | Debra | 519-448 | ||
119269 | Mr & Mrs | Glaister & Joycelyn | (514) 365 | ||
21299 | Mrs. | Nancy | 905-509 | (905) 509 | |
108076 | Mr & Mrs | Harold & Margaret | 519-744 | 519-744 | |
156979 | Ms. | Brenda | (506) 485 | ||
170218 | Ms. | Theresa | (905) 487 | ||
131266 | Mr & Mrs | Alan & Sheila | 306-373 | 306-373 | |
173208 | Ms. | Rhonda | (250) 765 |
It would not highlight columns A2 but rather randomly fill column A3.
Jun 27 2018 11:48 AM
It shall fill A3, that the only row where last two columns are empty.
Please check attached.
Jun 27 2018 11:51 AM