2 If Statements

I see this being talked about but still can't figure it out. I want the Status to change colors (Dots) based on days remaining but I also want Status to be overridden to Green (Dot) if the Approval is Green (up Arrow).

Current formula: =IF([Days Remaining]@row < 0, "Red", IF([Days Remaining]@row < 10, "Yellow", "Green"))

I just want to add: Make Green if Approval is Green (Up Arrow).

Best Answer

Answers

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

    Hi @Garet

    I hope you're well and safe!

    Try something like this.

    =IF([Days Remaining]@row = "", "", IF([Days Remaining]@row < 0, "Red", IF([Days Remaining]@row < 10, "Yellow", "Green")))

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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.

  • Sorry, still a little lost. I basically want the status to be green if the days remaining is over 10 day OR the approval is a green arrow. I want the green arrow to override any days remaining because it won't matter if it is approved.


  • Hi @Garet

    Did you try @Andrée Starå's formula? I'll break down what it says for you to make sure this works for what you want:

    =IF([Days Remaining]@row = "", "",

    ^If the Days Remaining cell is blank, then this will return a blank cell in your status column.

    IF([Days Remaining]@row < 0, "Red",

    ^ If the Days Remaining cell is less than 0, so a negative number, then return a Red status ball. (You can change this to be Red if it = 0 as well, by adding that in: <= 0, "Red")

    IF([Days Remaining]@row < 10, "Yellow",

    ^If the Days Remaining is greater than a negative number, but is less than 10, then return a Yellow status ball.

    "Green")))

    Otherwise, return a green ball. This means that if the Days Remaining are greater than 9 days (10 or more) then your row will return a green ball.


    It sounds like you do have an additional requirement with the green arrow. In this case, I would add a new statement at the beginning, right after the blank statement. Try this:

    =IF([Days Remaining]@row = "", "", IF(Approval@row = "Up", "Green", IF([Days Remaining]@row < 0, "Red", IF([Days Remaining]@row < 10, "Yellow", "Green"))))


    Cheers,

    Genevieve

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

  • Garet
    Garet
    Answer ✓

    Thank you. I did this: =IF(Approval@row = "Up", "Green", IF([Days Remaining]@row < 0, "Red", IF([Days Remaining]@row < 10, "Yellow", "Green"))) and worked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!