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

Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!
INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…