Health Column Formula For Dependencies
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
Help Article Resources
Check out the Formula Handbook template!