Issue applying conditional formatting to a symbols drop down

Sam Marritz

In the far left column of my sheet I have a "status" column which is a dropdown of yellow, red, and green symbols which indicate the general status of the project. I would like to apply conditional formatting which will automatically change this column to red/green/yellow based on conditions in the "difference" column, on the far right.

It seems that conditional formatting only lets me change certain things about a cell or row, like font and background color...is it possible to have conditional formatting apply to a dropdown menu?

Any help would be greatly appreciated.

Andrée Starå

Hi Sam,

Conditional formatting can only be used to change the appearance and not the RYG Status. You need to use a formula for that.

Try something like this. Change it to your conditions.

=IF(Difference@row < 0; "Red"; IF(Difference@row = 0; "Yellow"; IF(Difference@row > 0; "Green")))

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

=IF(Difference@row < 0, "Red", IF(Difference@row = 0, "Yellow", IF(Difference@row > 0, "Green")))

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

Did it work?

I hope that helps!

Have a fantastic weekend & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi Sam,

Conditional formatting can only be used to change the appearance and not the RYG Status. You need to use a formula for that.

Try something like this. Change it to your conditions.

=IF(Difference@row < 0; "Red"; IF(Difference@row = 0; "Yellow"; IF(Difference@row > 0; "Green")))

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

=IF(Difference@row < 0, "Red", IF(Difference@row = 0, "Yellow", IF(Difference@row > 0, "Green")))

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

Did it work?

I hope that helps!

Have a fantastic weekend & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Sam Marritz

Hey Andree!

Youve been amazingly helpful but I have one follow up question on this. Do I enter the formula into the status column itself? My first few attempts at this were read as "unparseable", but Im guessing thats just a syntax issue. Just wanted to check! thanks!

Sam Marritz

I figured it out!!!! thank you so much, your help was perfect I was just applying the formula to the wrong sheet so the column was unrecognized. Thanks again and happy holidays!

Andrée Starå

Excellent!

Happy to help!

Monica Ramos

Hi Andree,

I have a similar request so I tried to use the formula sample that you provided but I can't figure it out. I would like to determine the health of a task (red, yellow and green bubbles like the example from Sam) but this time it's based on whether the task is past due (red), due today (yellow), or due in the future (green).

Below is my formula pulling in the Due Date and (Start Date7) which is today's date.

=IF(Due Date@row < [Start Date]7; "Red"; IF(Due Date@row = [Start Date]7; "Yellow"; IF(Due Date@row > [Start Date]7 0; "Green")))

I'm open to any other ideas too. Thanks

Andrée Starå

Hi @Monica Ramos

I hope you're well and safe!

Excuse the late response.

Crazy times and I missed coming back to you.

Have you solved it, or do you still need help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!