+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting with multiple AND/OR criteria

  1. #1
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Conditional Formatting with multiple AND/OR criteria

    Hi there,
    I need some help conditionally formatting a spreadsheet.
    I need to highlight specific data within Column J based on data in four other columns.
    Basically i need it to say something like:

    Column J will be red if $B:$B="clean" AND $C:$C="floor" AND $G:$G is greater than or equal to 5 but less than 10 OR if $H:$H="yes"

    Is this even possible?

    thanks,
    camdameron

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Conditional Formatting with multiple AND/OR criteria

    After selecting the range you want to apply conditional formatting to (i.e. J1:J500);
    =OR($H2="Yes", AND( $B2= "Clean", $C2 = "floor", $G2 >=5, $G2 < 10))

    Does that work for you?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Conditional Formatting with multiple AND/OR criteria

    OK, so now I need to expand on this.

    i need an IF formula to capture the formula you gave above (thanks!) but expand upon it. see modifications below

    So, IF(=OR($H2="Yes", AND( $B2= "Clean" or b2="clean2", , $C2 = "floor", $G2 >=5, $G2 < 10)) then the true function = "alert" and the false action =""
    BUT i also need another true function to this where all criteria are the same, except the G column value is greater than 10.

    does this make sense??

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Conditional Formatting with multiple AND/OR criteria

    You didn't say what you wanted if G2>= 10. Put that where the ??? is.

    =IF(OR($H2="Yes", AND(OR($B2="Clean", $B2="clean2"),$C2="floor", $G2>=5)),IF($G2<10, "Alert", ???),"")

    Does that work for you?

  5. #5
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Conditional Formatting with multiple AND/OR criteria

    yeah. i think i am going to need to write a macro for this.
    i keep running across mroe data that needs to be included so a single cell formula might not be the best.

    basically what i need is to determine whether a sample is an alert or action based on different criteria.
    i have attached an example so you can see that each location's area has different levels associated with action and alert, so i am unsure how to get "action" or "alert" populated in column J

    can you just let me know whether i should look into writing a macro or if a formula will suffice? i think the formula will be too complicated and should probably apply to thentire worksheet.

    i really appreciate all your help!

    cameron
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Conditional Formatting with multiple AND/OR criteria

    We should be able to do this with Index and defined names. Why is MR1 in both the 2nd and 3rd table on sheet2?

  7. #7
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Conditional Formatting with multiple AND/OR criteria

    the third should be PR1
    i have attached the updated...
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Conditional Formatting with multiple AND/OR criteria

    Okay, it's a pretty long formula but appears to work. First it checks to see if it's got mold and is either CR or MR, then it's an automatic "Alert".
    I set up some defined names
    CRAir, MRAir, PRAir, CRSurface, MRSurface, PR Surface (horizontal)
    CRArea, MRArea, PRArea (vertical)
    If you go to the name box (where the cell id is, like A1), there's a dropdown with the names and clicking on them will take you to them.

    From there it was a matter of using INDIRECT, OFFSET and INDEX to pull back the correct response. I also added conditional formatting to make it easier to see. I think this could be simplified but this is where my brain took me today. The formula in J2 of sheet1 is.......

    =IF(AND(H2="Yes", OR(LEFT(B2,2)="CR", LEFT(B2,2)="MR")),"Alert",INDEX(Sheet2!$C$3:$E$3,MATCH(Sheet1!G2,OFFSET(INDIRECT(LEFT(Sheet1!B2,2)&A2),MATCH(Sheet1!C2,INDIRECT(LEFT(B2,2)&"Area"),0),0),1)))

    Does this work for you?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1