Formula for Health column

Sarah Lawrence

I would like the color of the circle in the Health column (formatted from a drop down list of RYGB balls) to change based on the status selected in the previous column containing a drop down list of "Not started", "In Progress", "Behind" and "Completed". Is this possible?

Andrée Starå

Hi @Sarah Lawrence

Yes, it’s possible.

You’d first need to decide what should show when.

How do you want it to work?

I hope that helps!

Be safe and have a fantastic week!

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!

Andrée Starå

Try one of these instead.

=IF(Status@row = "Not Started"; "Blue"; IF(Status@row = "In Progress"; "Yellow"; IF(Status@row = "Behind"; "Red"; IF(Status@row = "Completed"; "Green"))))

The same version but with the below changes for convenience.

=IF(Status@row = "Not Started", "Blue", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Behind", "Red", IF(Status@row = "Completed", "Green"))))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Andrée Starå

Hi @Sarah Lawrence

Yes, it’s possible.

You’d first need to decide what should show when.

How do you want it to work?

I hope that helps!

Be safe and have a fantastic week!

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!

Sarah Lawrence

Thank you! I would like a blue circle when Not Started is selected, a yellow circle when In Progress is selected, a red circle when Behind is selected and a green circle when Completed is selected. Can you provide the correct formula to be used to create these outcomes? Many thanks!

Andrée Starå

@Sarah Lawrence

Happy to help!

Try something like this. (change the column name to match yours)

=IF(Options@row = "Not Started"; "Blue"; IF(Options@row = "In Progress"; "Yellow"; IF(Options@row = "Behind"; "Red"; IF(Options@row = "Completed"; "Green"))))

Did it work?

Sarah Lawrence

I got an #Unparseable message using that formula after changing the column name to "Status" which is the column name I am using. I have the Health column setting set to symbols but not restricted to them.

Andrée Starå

Try one of these instead.

=IF(Status@row = "Not Started"; "Blue"; IF(Status@row = "In Progress"; "Yellow"; IF(Status@row = "Behind"; "Red"; IF(Status@row = "Completed"; "Green"))))

The same version but with the below changes for convenience.

=IF(Status@row = "Not Started", "Blue", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Behind", "Red", IF(Status@row = "Completed", "Green"))))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Sarah Lawrence

Thank you very much! The second option did exactly what I needed.

Andrée Starå

@Sarah Lawrence

Excellent!

You're more than welcome!