Formula to Change Harvey Ball Color Based off of a Word

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
Comments
-
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"))))
0 -
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"))))
0 -
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.
0 -
The brackets worked!!! Thank you all for your help!
0 -
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.
0 -
Absolutely! As Andree suggested you'll want to use brackets whenever referencing columns that have a space in them or end in a number.
0 -
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")
0 -
Capitalize the R in Red.
=IF([email protected] = "Complete", "Red")
Can you list out each of the 17 stages and what they should populate?
thinkspi.com
0 -
0
-
@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
0 -
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
0 -
-
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
0 -
@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
0 -
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")))
0