Solved: Excel - Conditional formatting based on multiple yes/no

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

dmschave

Thread Starter
Joined
Sep 23, 2008
Messages
4
Hiya Guys,

My question:

I need to color a cell (merged) based on yes/no data in a column range. I already have a simple conditional for the "Complete" rows to turn green if "yes", but I need the supplier to turn green if ALL of the "Complete" rows are "yes" and there are no "no" (and ignore blanks).

My current seems to only apply to the first field in the range and I have no idea how to make it ignore blanks. Any help is appreciated!
 

dmschave

Thread Starter
Joined
Sep 23, 2008
Messages
4
Can do.

Here is a snippet. The goal is to have the A,B,C fields only change to green when all the items under "Complete" are "yes" (excluding blanks). In the example, A and B should be green and C and D should be no color. Everything I have tried to this point either fails to check the entire range or does not exclude blanks.

Thanks
 

Attachments

Joined
Dec 4, 2007
Messages
2,895
I need the supplier to turn green if ALL of the "Complete" rows are "yes" and there are no "no" (and ignore blanks).
When you say that you want the supplier to turn green if all are "yes", do you mean only for the columns that pretain to the supplier, or do you mean th entire B column?

If all cells are "yes" but one cell is "blank" what color should the Supplier cell be?
 
Joined
Sep 18, 2008
Messages
22
Hi,

If I understand correctly, you want the cell with supplier name to turn green if all other cells were yes? I based this example on 3 yes cells being required.

=IF(AND(D6="yes",E6="yes",F6="yes"),TRUE,FALSE)

This formula would give a result of true if 3 cells all indicated "yes". Any other combination of yes or no would result in false. The supplier cell could then be set with conditional formatting to green if results cell indicated true.


Regards - Nic
 
Joined
Sep 24, 2007
Messages
869
Can do.

Here is a snippet. The goal is to have the A,B,C fields only change to green when all the items under "Complete" are "yes" (excluding blanks). In the example, A and B should be green and C and D should be no color. Everything I have tried to this point either fails to check the entire range or does not exclude blanks.

Thanks
Something like this will do what you need or no?
 

Attachments

dmschave

Thread Starter
Joined
Sep 23, 2008
Messages
4
1) yes i manually enter yes/no

2) Supplier should be tied only to its own range of "complete" for green/no green

3) If all cells are "yes" or blank then the supplier should be green. Only a "no" should prevent supplier from being green.

4) Nic - Would this take into account ignoring blanks? Also is there a way to run this check as a range (ex: B8:B17 ) rather than individually calling out each cell?

thanks guys
-D
 
Joined
Sep 24, 2007
Messages
869
Yes it sure does! Thanks!
I suppose you have more than this data, so look at the conditional formating!
If you have the same row number for each supplier than it would be easier to update it for your workbook
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top