How can I show a colored health dot based on the other colored dots in the same column?

jcabaniss

I am trying to have a cell show a colored health dot based on the other dots in the same column. This cell basically shows red if there any red dots, yellow if there are no red dots and at least one yellow, green if there are no reds or yellows, and blue if all other dots are blue.

This cell called out should have a red dot based on the red dot in the Health column:

I can get it to work with a single color but don't know how to string together all the possible outcomes. It would also be ideal if the main health indicator could be blank if the status of that row is Not Started (like it is in the screenshot).

Thank you for any assistance.

jcabaniss

I think I at least figured something out. This seems to work, and it even works to change the status to blue (complete) when the status is changed to complete:

=IF(Status@row = "Complete", "Blue", IF((COUNTIFS(Health2:Health72, "Red")) > 0, "Red", IF((COUNTIFS(Health2:Health72, "Yellow")) > 0, "Yellow", IF((COUNTIFS(Health2:Health72, "Green")) > 0, "Green", IF(Status@row = "Complete"), "Blue"))))

It works, but is there an easier way?

Nick Korna

Hi @jcabaniss,

Have a look into using the following as an alternative so you can skip needing to manually alter row ranges:

https://help.smartsheet.com/function/children

https://help.smartsheet.com/function/descendants

In your example, you're not looking at the child rows, but the descendant ones, so the following would likely do what you're after:

=IF(Status@row = "Complete", "Blue", IF(COUNTIF(DESCENDANTS(Health@row), "Red") > 0, "Red", IF(COUNTIF(DESCENDANTS(Health@row), "Yellow") > 0, "Yellow", IF(COUNTIF(DESCENDANTS(Health@row), "Green") > 0, "Green",""))))

Alternatively, you can use the CHILDREN to turn this into a column formula. For example:

=IF(COUNTIF(CHILDREN([Health 2]@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN([Health 2]@row), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN([Health 2]@row), "Green") > 0, "Green", IF(Status@row = "Complete", "Green", IF(Status@row = "In Progress", "Yellow")))))

This would need additional criteria to decide which of the "In Progress" should be yellow vs. red, but it's a start.

Example, with the first formula in the Health column for rows where the task is 1 (1, 1a, 1b, 1c) and the second formula in the Health 2 column:

If you want some help developing the 2nd formula so you can use it throughout, I'd just need to know the criteria to distinguish.

Hope this helps, but if you've any questions/problems, etc. then just post! 😊