Make RYGB ball color change with status

Amanda_S

I would like the ball color to automatically change with the status, but I am having trouble with the exact formula.  For example, I want the colors to follow a similar legend as follows:

Not Started - Yellow

In Progress - Blue

Complete - Green

At Risk - Red

 

Would it be an IF (true, false) formula or is there something else? 

MattH

You would use a nested if statement. 

 

=IF(Column1, ="Not Started", "Yellow", IF(Column1, ="In Progress", "Blue", IF(...etc

 

I strongly recommend that the status is a drop down so that the formula never misses a typo. 

Amanda_S

Thank you.  It worked!

Susan@SMC

Hi Matt,

Your post is the closest to what I'm trying to do, but I'm not quite there yet. I'm trying to automate RYGB status ball colors to link to the following options from a "Status" column drop-down list, as follows:

Green = In Progress

Yellow = Potential Delay

Red = Behind Schedule

Blue = Complete

Based on your recommended formula, here's what I've come up with, but it's giving me an "#Unparseable" error.

=IF(Status3, ="In Progress", "Green"), IF(Status3, ="Potential Delay", "Yellow"), IF(Status3, ="Behind Schedule", "Red"), IF(Status3, ="Complete", "Blue")

Can you tell me what I'm doing wrong?

Kind Regards,

Susan

Andrée Starå

Hi Susan,

I answered in your new post as well!

Try this.

=IF(Status@row = "In Progress"; "Green"; IF(Status@row = "Potential Delay"; "Yellow"; IF(Status@row = "Behind Schedule"; "Red"; IF(Status@row = "Complete"; "Blue"))))    

The same version but with the below changes for your and others convenience.  

=IF(Status@row = "In Progress", "Green", IF(Status@row = "Potential Delay", "Yellow", IF(Status@row = "Behind Schedule", "Red", IF(Status@row = "Complete", "Blue"))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting