Formula to Change Harvey Ball Color Based off of a Word

Athena Hathaway
Athena Hathaway ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I've seen so many examples of "Nesting IF" formulas for changing a Harvey Ball to a different color based off of multiple numeric or date scenarios. What I haven't come across is a way to change the ball color based off of verbiage:

Example: I change a cell to Rejected and the ball turns Red. I change the same cell to approve, and the ball turns green.

I have four status' (Pending Approval, Returned, Approved, Rejected) that I'd like to automatically drive the color change of the Harvey Balls (Blue, Yellow, Green, Red).

Any suggestions?

Thank you

«1

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Make sure your column type is set to "symbol" with the RYGB ball type by right-clicking on the column header and editing the properties. Then try this formula. If your status column is titled something different then use that title instead... 

    =IF([email protected] = "Pending Approval", "Blue", IF([email protected] = "Returned", "Yellow", IF([email protected] = "Approved", "Green", IF([email protected] = "Rejected", "Red"))))

  • Athena Hathaway
    Athena Hathaway ✭✭✭✭✭✭

    Thank you so much for responding. My column is set to the RYGB ball symbol. I tried your formula and replaced the "Status" with my actual column name "Approval Status One". However, it returned the dreaded #UNPARSEABLE. Did I add my column name in the wrong spot? Thank you again for your guidance. 

     

    =IF(Approval Status [email protected] = "Pending Approval", "Blue", IF(Approval Status [email protected] = "Returned", "Yellow", IF(Approval Status [email protected] = "Approved", "Green", IF(Approval Status [email protected] = "Rejected", "Red"))))

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

    Hi Athena,

    Try enclosing your column names in square brackets. [ ]

    Did it work?

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

  • Athena Hathaway
    Athena Hathaway ✭✭✭✭✭✭

    The brackets worked!!! Thank you all for your help! :)

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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Absolutely! As Andree suggested you'll want to use brackets whenever referencing columns that have a space in them or end in a number. 

  • So i have tried some of the suggetions above, but instead of the color changing, it just changes it to text.  

    =IF([email protected] = "complete", "red")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @lesmickin

    Capitalize the R in Red.

    =IF([email protected] = "Complete", "Red")


    @almrie

    Can you list out each of the 17 stages and what they should populate?

    thinkspi.com

  • ALMF
    ALMF
    edited 01/02/20


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @almrie I received an email notification that you had replied, but when I come to the post to see your comment there is nothing there and it says "edited".

    thinkspi.com

  • Yes, an admin moved this topic to another thread. Apologies, I'm new to this community and I'm not sure where to comment when the topic has been split. Below is what I replied originally:

    NEED INFO, Empty

    ASSIGN, Empty

    BRIEF RECEIVED, Quarter

    PRE-PRODUCTION, Quarter

    PRODUCTION, Half

    APPROVAL, Three Quarter

    REVISIONS, Three Quarter

    REVIEW, Three Quarter

    SFX/GFX, Half

    DELIVERED, Full

    KILLED, Empty

    LEGAL, Three Quarter

    N/A, Empty

    RFD, Three Quarter

    HOLD, Empty

    TBD, Empty

    QC, Three Quarter

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @almrie

    Since an admin moved it to another thread, I will respond there.

    thinkspi.com

  • I need help with the formula for my sheet. I need the symbol to change color if I change the status.

    In progress = blue

    Complete = green

    Ongoing = yellow

    Need confirmation = red


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jennifer Cosgrove Try something like this...

    =IF([email protected] = "In Progress", "Blue", IF([email protected] = "Complete", "Green", IF([email protected] = "Ongoing", "Yellow", "Red")))

    thinkspi.com

  • I love the solution and I am wondering if anyone can help me further it one step, I need the colors to be "red" if the status (in my case state) is not finished and the end date has passed.. im not quite sure how to formulate.. I have this so far and works as planned but I need to add the second part

    =IF([email protected] = "Complete", "Green", IF([email protected] = "Not Started", "Blue", IF([email protected] = "In Progress", "Yellow")))