+ Reply to Thread
Results 1 to 14 of 14

Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

  1. #1
    Forum Contributor
    Join Date
    03-15-2016
    Location
    Arnhem, The Netherlands
    MS-Off Ver
    2007
    Posts
    102

    Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    excel.jpg

    I want to use conditional formatting to automatically turn the background red when an sample contains asbestos (ignore the PCB line).
    However i want the formula to first check the D kolom if it concerns asbestos i used the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    , and it worked so far, but now after that i want it to check the H kolom (using OR?) if it is either:

    -Partially contained
    -Contained
    -PCHM

    So basically i need the formula to check if it concerns an asbestos application, and then after that check if it either is one of those 3 terms i used above, and if so the background should turn red.
    If it is one of the below described descriptions, i want the lay out to stay as it is:

    - not contained
    - < tresh det
    - or anything else

    Is there anyone that can help me out? thanks in advance!
    Attached Images Attached Images

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    Try

    =AND($D5="Asb",OR($H5="Partially contained",$H5="Contained",$H5="PCHM"))

  3. #3
    Forum Contributor
    Join Date
    03-15-2016
    Location
    Arnhem, The Netherlands
    MS-Off Ver
    2007
    Posts
    102

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    I get an error when i type that, it says the formula contains an mistake. (i did replace AND for EN, and OR for OF, since those are the dutch commands and i work in an dutch version), i did also try the command as you said it. I tried both twice to make sure i didn't make any typo's, i will try a third time now, but i don't have any high hopes. Any suggestions? Thanks for the quick response btw!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    Also try changing the commas to semicolons ( , to ; )

  5. #5
    Forum Contributor
    Join Date
    03-15-2016
    Location
    Arnhem, The Netherlands
    MS-Off Ver
    2007
    Posts
    102

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    I tried copying your formula and it allowed me to proceed, which was a learning moment, because i was under the impression i had to do all the commands in dutch (quite annoying if you're looking up how to actually do it on the internet). However the cells don't get the color (i did assign the red color). I've also tried changing the symbols as you suggested. Any more suggestions?

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    The only think I notice is the value in H5 is actually "partially cont."
    That isn't an exact match to "partially contained"

    Check over those cells in column H to make sure they match exactly with the 3 strings in the OR function.

  7. #7
    Forum Contributor
    Join Date
    03-15-2016
    Location
    Arnhem, The Netherlands
    MS-Off Ver
    2007
    Posts
    102

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    I already noticed that i mentioned that string incorrectly in my first post, but i changed that in the formula from the start:

    =AND($D5="Asb";OR($H5="partially cont.";$H5="contained";$H5="PCHM"))

    Note: i already tried to remove the capital letters in the formula, since i am not aware wether or not they are capital sensitive.
    Note 2: i also tried going back to comma's, but then i get the invalid formula error again.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    Capitalization won't matter in this formula. "Contained" will be considered the same as "contained"
    And yes, you need to stick with the ;
    It's a regional thing, some people's computers use commas, some use semicolons (it's a setting in Control Panel - Region and Language - Additional Settings, List seperator)

    So the formula looks correct.
    The only think that would be making it fail is if the cells in H don't match exactly (spelling only, not case sensitive)
    Check those cells for extra spaces, like "contained" vs " contained" or "contained "

  9. #9
    Forum Contributor
    Join Date
    03-15-2016
    Location
    Arnhem, The Netherlands
    MS-Off Ver
    2007
    Posts
    102

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    I found the problem, apparently it does matter if i use the English or dutch commands.

    Changed it to:
    =EN($D5="Asb";OF($H5="partially cont.";$H5="contained";$H5="PCHM"))

    Works like a charm, thanks a lot!!!

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    Ah yes, Of Coarse..

    If the formula was entered as a standard cell formula, it would have evaluated to #Name? error.
    But you won't see that in conditional formatting, it would just be considered NOT True.

    Glad to help, thaks for the feedback.

  11. #11
    Forum Contributor
    Join Date
    03-15-2016
    Location
    Arnhem, The Netherlands
    MS-Off Ver
    2007
    Posts
    102

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    That explains why my =ROW command showed the #name error. I had been struggeling with that for a while aswell.

    Now i wonder if it is possible to expand this formula to actually make the background blue if it contains PCB, and brown if for heavy metals, green for ODS (depending on the first factor "Asb", if that were to be PCB? I think it would get quite complicated to get that to work, if that is even actually possible.
    I could ofcourse use the same formula (change asb in PCB for instance), then change the color in the rows that concern those other substances, but we sometimes sample something for PCB that we usually sample for asbestos.
    As you might have noticed by now, i am quite new with formulas and such, but i am just wondering if this is possible or i just should accept that won't work and continue as planned?

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    You'd have to create seperate conditional format formulas for each different color you want.
    You can't have 1 conditional format formula that says (if condition A, then blue, if condition B, then red etc)

  13. #13
    Forum Contributor
    Join Date
    03-15-2016
    Location
    Arnhem, The Netherlands
    MS-Off Ver
    2007
    Posts
    102

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    Ah, that's a bit of a bummer, but i am quite impressed i actually managed to come this far, with your help ofcourse.
    Well, i will thank you one more time and consider this problem solved.
    I will be back with more problems soon enough (in a new thread ofc).

    Thanks a lot!!!

  14. #14
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional formating with a formula: 1 factor is met and if one of 3 factors is met.

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Factor Up and Factor Down Values Formula
    By alive555 in forum Excel General
    Replies: 6
    Last Post: 08-14-2015, 12:01 PM
  2. Replies: 4
    Last Post: 01-23-2014, 10:14 AM
  3. Replies: 0
    Last Post: 02-17-2012, 07:33 PM
  4. If Formula and Conditional Formating
    By AznDragon533 in forum Excel General
    Replies: 1
    Last Post: 04-04-2011, 11:50 PM
  5. Conditional formating formula
    By jacko311 in forum Excel General
    Replies: 12
    Last Post: 02-02-2010, 12:04 PM
  6. Replies: 6
    Last Post: 07-31-2009, 10:58 AM
  7. Conditional formating-What formula do i need to use in conditional formating
    By warrima in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2009, 12:33 AM

Tags for this Thread

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