Automate RYG Balls based on 3 columns.

CaKing
edited 12/09/19 in Smartsheet Basics

Hi, 

 

I need help with finding a way to automate the RYG balls so that if no dates are input, it's white. If the first date is input, it's red. If the second date is input, its yellow. And if the final date is input, it's green.

 

Thank you !

 

status.JPG

Comments

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

    Hi,

    Try something like this.

    =IF(AND(ISBLANK([Welcome Email]@row); ISBLANK([email protected]); ISBLANK([email protected])); ""; IF(ISDATE([email protected]); "Green"; IF(ISDATE([email protected]); "Yellow"; IF(ISDATE([Welcome Email]@row); "Red"))))

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

    =IF(AND(ISBLANK([Welcome Email]@row), ISBLANK([email protected]), ISBLANK([email protected])), "", IF(ISDATE([email protected]), "Green", IF(ISDATE([email protected]), "Yellow", IF(ISDATE([Welcome Email]@row), "Red"))))

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

    Did it work?

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Wow! It works perfectly!

    Many thanks :)

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

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • I'm sorry to have another question, perhaps you can advise me why when I changed the order and updated the form's names, the ball doesn't change to green when the welcome email date is present?

     

    You can see in the photo, the blank, gray and yellow work, but not green. 

     

    =IF(AND(ISBLANK([Welcome Email]@row), ISBLANK([TPSA Signed]@row), ISBLANK([Counter Signed]@row)), "", IF(ISDATE([Counter Signed]@row), "Yellow", IF(ISDATE([TPSA Signed]@row), "Gray", IF(ISDATE([Welcome Email]@row), "Green"))))

     

    I changed it to RGYG 

     

    Thank you :)

    Capturestastus.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is the order in which you have your conditions. Once it comes across it's first true value, it stops evaluating. So if yellow and green are both true, but yellow comes first, it will trigger a yellow response, but if green comes first in your formula, it will trigger a green response. Try working your formula in order of priority...

     

    =IF(ISDATE([Welcome Email]@row), "Green", IF(ISDATE([Counter Signed]@row), "Yellow", IF(ISDATE([TPSA Signed]@row), "Gray", "")))

    thinkspi.com

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

    Happy to help!

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.