Formula to Change Harvey Ball Color Based off of a Word

Athena Hathaway

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

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

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å

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

Athena Hathaway

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

Andrée Starå

Excellent!

Happy to help!

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. 

lesmickin

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

@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?

ALMF

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

ALMF

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

@almrie

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

Jennifer Cosgrove

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

@Jennifer Cosgrove Try something like this...

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

Nathan Betters

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")))