Health Column taking Predecessors Column into consideration to determine color.

hflorez

I have the following formula in the health column.

=IF(OR(Status@row = "Completed", [End Date]@row - TODAY() >= 7), "Green", IF([End Date]@row - TODAY() <= -1, "Red", "Yellow"))

It works fine. If End date is more than 7 days out then the health column is green, If End Date has passed then Red.

The problem is that I didn't take into account predecessors. In Row 7 you see the End Date is 12/07/22 and Health is RED. Row 8 has a dependency of Row 7 and because I got the above formula in the Column Formula it sees the End Date of 12/21/22 and thinks it should be green, when I want it to actually be red because its dependency is red. I believe I need multiple formulas in the Health Column, the above formula as one, and another formula stating that if there are dependencies, look at the health column of that predecessor and make the health identical. So Row 8 Health status should be Red.

I am new to formulas and lost, please help. Attached is a screenshot

Darren Mullen

@hflorez I implemented a solution a while back that took into account predecessor columns. I don't have access to that sheet anymore. (was a client project that I don't have access to any longer). It took several helper columns. Hard to describe on the community... but it is doable... just takes time and some Smartsheet gymnastics.

If this helps, I used a column formula to dynamically calculate each row's row # which would match the predecessor, and then used Index Match to get the value from the row that matched the predecessor.

If you have multiple predecessors in a row, that can be handled too, but takes more formulas and helpers.