RYGB symbols automated to reflect status/progress

JWattz
JWattz
edited 12/09/19 in Formulas and Functions

Hi there, Attempting to write formula to automate RYGB symbols when task is In Progress, On Hold or Complete. I've prepared a few formulas and none seem to be accurate. If someone would take a look at below attempts and let me know what needs to be changed, I'd greatly appreciate it!

None of below worked:

=IF(OR([Status]6 = In Progress, ”Blue”,

IF ([Status]6 = On Hold, ”Yellow”,

If ([Status]6 = Complete, ”Green”)))

 

=IF([Status]6 = In Progress, ”Blue”,

IF ([Status]6 = On Hold, ”Yellow”,

If ([Status]6 = Complete, ”Green”)))

 

=IF(Status6 = In Progress, ”Blue”,

IF ([Status]6 = On Hold, ”Yellow”,

If ([Status]6 = Complete, ”Green”)))

Then, attempting using numbers rather than text:

=IF ([Status Numeric]6 = 2, ”Blue”,

IF ([Status Numeric]6 = 3, ”Yellow”,

If ([Status Numeric]6 = 4, ”Green”)))

Thanks!

Comments

  • SYSPK
    SYSPK ✭✭✭✭✭✭

    All of your texts like "complete," "on hold," etc.. need to be in quotation marks. 

    Also, I see an OR formula up there but it doesn't seem necessary, because you want different things to happen for the different statuses. If you wanted the same thing to happen, like if you want the ball to turn blue if the status was "in progress" or if the status was "on hold," then you would use OR. But it looks to me like you want different things for each. You'd use a nested IF like this, and put all of your conditions in quotation marks.

    IF([status]6 = "in progress", "blue", IF([status]6 = "on hold", "yellow", IF (etc...

    You don't need to worry about counting the right amount of ending parentheses at the end, )))))), because if you leave those blank the sheet will fill it in for you. So long as you set it up correctly in the beginning of all the IFs.

     

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

    Hi,

    Try something like this.

    =IF([email protected] = "In Progress","Blue")

    Did it work?

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your second and third should work once you add quotes around the criteria such as "On Hold", "Complete", etc...

     

    Your fourth SHOULD be working as is assuming the values in the [Status Numeric] column are in fact numerical values.

    thinkspi.com

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭

    It won't be liking the space between the =IF and the ( ...

  • Hi there! Thank you for your reply! The info you shared assisted, below is the formula that ended up working thanks to you and everyone who replied!

    =IF(Status12 = "In Progress", "Blue", IF(Status12 = "Complete", "Green", IF(Status12 = "On Hold", "Yellow", IF(Status12 = "Risk", "Red"))))

    Thank you!

  • Hi there! Thank you for your reply! The info you and everyone shared assisted, below is the formula that ended up working thanks to you and everyone who replied!

    =IF(Status12 = "In Progress", "Blue", IF(Status12 = "Complete", "Green", IF(Status12 = "On Hold", "Yellow", IF(Status12 = "Risk", "Red"))))

    Thank you!

  • Hi there! Thank you for your reply! The info you and everyone shared assisted, below is the formula that ended up working thanks to you and everyone who replied!

    =IF(Status12 = "In Progress", "Blue", IF(Status12 = "Complete", "Green", IF(Status12 = "On Hold", "Yellow", IF(Status12 = "Risk", "Red"))))

    Thank you!

  • Hi there! You were right! Thank you for your reply! The info you and everyone shared assisted, below is the formula that ended up working thanks to you and everyone who replied!

    =IF(Status12 = "In Progress", "Blue", IF(Status12 = "Complete", "Green", IF(Status12 = "On Hold", "Yellow", IF(Status12 = "Risk", "Red"))))

    Thank you!

  • 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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent! Happy to help! yes

    thinkspi.com

  • Could you help me with my formula it has some of the characteristics within this thread


    =IF([Completion Status]3 = "In Progress", "Yellow", "Red"),IF([Completion Status]3="Completed","Green"))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Leslye Jackson Try something like this...

    =IF([Completion Status]@row = "Completed", "Green", IF([Completion Status]@row = "In Progress", "Yellow", "Red"))

    thinkspi.com