Formula to show if certain cells are blank change the status to red, yellow, or green

I have a sheet where we want to show if the columns successor 1, 2, and 3 are blank the status column should be red.

If only successor 1 is filled out status should be yellow

If successor 1 and 2 are filled out staus should also be yellow

If all 3 successor columns are filled out the status should be green. Pretty much trying to show the status of completion for these columns.

I've tried if(not(isblank and a few other formulas and can't find the right way to write this. Does anyone have any ideas?


Best Answer

Answers

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

    Hi Taylor,

    Try something like this.

    =IF(OR([Successor 1]@row <> ""; [Successor 2]@row <> ""); "Yellow"; 
    IF([Successor 3]@row <> ""; "Green"))
    

    The same version but with the below changes for your and others convenience.

    =IF(OR([Successor 1]@row <> "", [Successor 2]@row <> ""), "Yellow", 
    IF([Successor 3]@row <> "", "Green"))
    

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Would that work?

    I hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it 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.

  • That has definitely worked better than anything I've tried so far! i'm now just running into the problem where if I have anything in the successor 1 column it will keep it Yellow, even when I fill something into the Successor 3 column.

  • That worked!! Thank you so much!

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

    Excellent!

    Happy to help!

    Here's the formula for others reference.

    =IF([Successor 3]@row <> ""; "Green"; IF(OR([Successor 1]@row <> ""; [Successor 2]@row <> ""); "Yellow"))

    The same version but with the below changes for your and others convenience.

    =IF([Successor 3]@row <> "", "Green", IF(OR([Successor 1]@row <> "", [Successor 2]@row <> ""), "Yellow"))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Remember! Did my post help answer your question or solve your problem? Please help the Community by marking it 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!