Nested IF/AND/OR

I'm modifying a formula used for status tracking and I'm running into an unparseable error that I'm having problems resolving. I want to make the status red (stop sign) if the status is "in progress" or "not started" when the "at risk" column is checked. My understanding is that the formula will look for the first true statement and then stop looking.

Original formula:

=IF(Summary@row = 1, "", IF(Status@row = "Complete", "☑️", IF(Status@row = "Cancelled", "✖️", IF(AND(OR(Start@row = "", [Planned Finish]@row = ""), Status@row = "In Progress"), "🟢", IF(OR(Start@row = "", [Planned Finish]@row = ""), "🔘", IF(AND(OR(Status@row <> "Completed", Status@row <> "Completed", Status@row <> "Completed"), [Planned Finish]@row < Today@row), "🛑", IF(AND(Status@row = "Not Started", Start@row < Today@row), "⚠️", IF(Status@row = "In Progress", "🟢", "🔘"))))))))

Revised formula (addition after cancelled icon)

=IF(Summary@row = 1, "", IF(Status@row = "Complete", "☑️", IF(Status@row = "Cancelled", "✖️", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [At Risk]@row = 1), "🛑"), IF(AND(OR(Start@row = "", [Planned Finish]@row = ""), Status@row = "In Progress"), "🟢", IF(OR(Start@row = "", [Planned Finish]@row = ""), "🔘", IF(AND(OR(Status@row <> "Completed", Status@row <> "Completed", Status@row <> "Completed"), [Planned Finish]@row < Today@row), "🛑", IF(AND(Status@row = "Not Started", Start@row < Today@row), "⚠️", IF(Status@row = "In Progress", "🟢", "🔘")))))))))

Any ideas?

Tags:

Answers

  • .... and I found a solution: There was an extra closing parenthesis after the first stop sign icon


    =IF(Summary@row = 1, "", IF(Status@row = "Complete", "☑️", IF(Status@row = "Cancelled", "✖️", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [At Risk]@row = 1), "🛑", IF(AND(OR(Start@row = "", [Planned Finish]@row = ""), Status@row = "In Progress"), "🟢", IF(OR(Start@row = "", [Planned Finish]@row = ""), "🔘", IF(AND(OR(Status@row <> "Completed", Status@row <> "Completed", Status@row <> "Completed"), [Planned Finish]@row < Today@row), "🛑", IF(AND(Status@row = "Not Started", Start@row < Today@row), "⚠️", IF(Status@row = "In Progress", "🟢", "🔘")))))))))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @jking108

    I hope you're well and safe!

    Excellent! Glad you got it working!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!