1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by dmschave, Sep 23, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. dmschave

    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!
     
  2. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    dmschave, welcome to the forum. :)

    If possible, could you attach a sample file (with dummy data if needed)?
     
  3. dmschave

    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
     

    Attached Files:

  4. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Do you manually enter "yes" or "no"?
     
  5. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    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?
     
  6. Nic Cunliffe

    Nic Cunliffe

    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
     
  7. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Something like this will do what you need or no?
     

    Attached Files:

  8. dmschave

    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
     
  9. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    AJ, that is great. Everytime I look at one of your post, I learn something new.
     
  10. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    :rolleyes:
     
  11. dmschave

    dmschave Thread Starter

    Joined:
    Sep 23, 2008
    Messages:
    4
    Yes it sure does! Thanks!
     
  12. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    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
     
  13. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    LOL...I am only telling the truth. (y) :D
     
  14. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/752583

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice