If AND or OR

Hi,

I have use below formula to show all parts of my process steps. It works well with red and green balls. But I would like to add if one of below columns is yellow (pending) the result in this cell should be yellow as well.

How can I modify the fomula:

=IF([Dev sample needed?]@row = "", "", IF(OR([CAD upload on time ?]@row = "Green", [Pattern confirmed on time?]@row = "Green", [Tooling confirmed on time?]@row = "Green", [Materials confirmed on time?]@row = "Green"), "Green", "Red"))

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Christine Menke

    I believe your'e getting an error because of the spaces between the IF statements and their open parentheses.

    IF( versus IF (

    Try this:

    =IF([Dev sample needed?]@row = "", "", IF(AND([CAD upload on time ?]@row = "Green", [Pattern confirmed on time?]@row = "Green", [Tooling confirmed on time?]@row = "Green", [Materials confirmed on time?]@row = "Green"), "Green", IF(OR([CAD upload on time ?]@row = "Yellow", [Pattern confirmed on time?]@row = "Yellow", [Tooling confirmed on time?]@row = "Yellow", [Materials confirmed on time?]@row = "Yellow"), "Yellow", "Red")))


    Keep in mind that if you are looking into Status Symbol ball columns, you'll need to spell the text as "Yellow" with only one capital in order to display the ball, or recognize the ball colour. Otherwise it will look for the text, "YELLOW". Does that make sense?


    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Rich Stowell
    Rich Stowell ✭✭✭✭✭

    Christine,

    First, your IF(OR statement means that if any of the column are green the you are reporting your process is Green - is this what you intend? If this is the case, it will be hard to get past this step in the logic as a "true" result will occur. Suggest you change this to "AND" so that ALL columns must be green before reporting the Process as "Green". If you do this, the try the following to report "Green", "Yellow" or "Red":

    =IF (

    [Dev sample needed?]@row = "",

    "", 

    IF (

    AND (

    [CAD upload on time ?]@row = "Green", 

    [Pattern confirmed on time?]@row = "Green", 

    [Tooling confirmed on time?]@row = "Green", 

    [Materials confirmed on time?]@row = "Green"

    ), 

    "Green", 

    IF (

    OR (

    IF (

    [CAD upload on time ?]@row = "Yellow",

    "True",

    "False"

    ), 

    IF (

    [Pattern confirmed on time?]@row = "Yellow", 

    "True",

    "False"

    ), 

    IF (

    [Tooling confirmed on time?]@row = "Yellow", 

    "True",

    "False"

    ), 

    IF (

    [Materials confirmed on time?]@row = "Yellow"

    "True",

    "False"

    ), 

    ), 

    "Yellow", 

    "Red"

    )

    )

    )

  • Thanks for your help Rich Stowell ,

    but it's still not working. I get an unparseable error. Any idea to fix it?

    =IF ([Dev sample needed?]@row= "","", IF (AND ([CAD upload on time ?]@row= "GREEN", [Pattern confirmed on time?]@row = "GREEN", [Tooling confirmed on time?]@row= "GREEN", [Materials confirmed on time?]@row= "GREEN"), "GREEN", IF (OR (IF ([CAD upload on time ?]@row = "YELLOW", "TRUE" , "FALSE"), IF ([Pattern confirmed on time?]@row= "YELLOW", "TRUE","FALSE"), IF ([Tooling confirmed on time?]@row = "YELLOW", "TRUE", "FALSE"), IF ([Materials confirmed on time?]@row= "YELLOW", "TRUE","FALSE"),), "YELLOW", "RED")))

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Christine Menke

    I believe your'e getting an error because of the spaces between the IF statements and their open parentheses.

    IF( versus IF (

    Try this:

    =IF([Dev sample needed?]@row = "", "", IF(AND([CAD upload on time ?]@row = "Green", [Pattern confirmed on time?]@row = "Green", [Tooling confirmed on time?]@row = "Green", [Materials confirmed on time?]@row = "Green"), "Green", IF(OR([CAD upload on time ?]@row = "Yellow", [Pattern confirmed on time?]@row = "Yellow", [Tooling confirmed on time?]@row = "Yellow", [Materials confirmed on time?]@row = "Yellow"), "Yellow", "Red")))


    Keep in mind that if you are looking into Status Symbol ball columns, you'll need to spell the text as "Yellow" with only one capital in order to display the ball, or recognize the ball colour. Otherwise it will look for the text, "YELLOW". Does that make sense?


    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Hi @Genevieve P

    thanks a lot for explaining the small details. It works now :-)

    Have a good day!

  • Hi Christine, I'm glad I could help!

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!