Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 13 of 13 Posts

·
Registered
Joined
·
4 Posts
Discussion Starter · #1 ·
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!
 

·
Registered
Joined
·
4 Posts
Discussion Starter · #3 ·
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

·
Registered
Joined
·
2,895 Posts
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?
 

·
Registered
Joined
·
22 Posts
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
 

·
Registered
Joined
·
869 Posts
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

·
Registered
Joined
·
4 Posts
Discussion Starter · #8 ·
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
 

·
Registered
Joined
·
869 Posts
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
 
1 - 13 of 13 Posts
Status
Not open for further replies.
Top