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

Find more posts tagged with

Formulas

Project Management

Comments

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(status@row = "Pending Approval", "Blue", IF(status@row = "Returned", "Yellow", IF(status@row = "Approved", "Green", IF(status@row = "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 One@row = "Pending Approval", "Blue", IF(Approval Status One@row = "Returned", "Yellow", IF(Approval Status One@row = "Approved", "Green", IF(Approval Status One@row = "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(Status@row = "complete", "red")

Paul Newcome

@lesmickin

Capitalize the R in Red.

=IF(Status@row = "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(Status@row = "In Progress", "Blue", IF(Status@row = "Complete", "Green", IF(Status@row = "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(State@row = "Complete", "Green", IF(State@row = "Not Started", "Blue", IF(State@row = "In Progress", "Yellow")))

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…