Health Column Formula For Dependencies

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

Julio S.

Hi @hflorez,

I've been researching past Community posts and I was able to find this with a similar request to yours. However, there is no way with the current functionality that your formulas can take predecessors into consideration when performing calculations, when you have a moment, please submit your feature request to the Product team by signing in to the online Community (the Community uses your Smartsheet account to sign in) and create an Idea post in the Smartsheet Product Feedback and Ideas topic. 

Posting your enhancement in the Community will allow other Smartsheet users to see and vote on your idea! The top-voted posts in this category are reviewed monthly by the Product team and you'll receive an email notification if a status changes for a post you've created or voted on. 

The only alternative that I can think of would be to create parent roll-ups to divide your projects in phases and create a Column formula in the Health Column that discriminates whether the row is parent or child and calculate its value accordingly. See my example below where if the Column is parent it will count how many tasks are Red and if any of them is, a Red overall value will be assigned. For child rows, I've used your formula which only considers it's own dates. This would quickly flag any phase in the project which has any red values:

Formula: =IF(COUNT(ANCESTORS(Status@row)) = 0, IF(COUNTIF(CHILDREN(Health@row), "Red") > 0, "Red", ""), IF(OR(Status@row = "Completed", [End Date]@row - TODAY() >= 7), "Green", IF([End Date]@row - TODAY() <= -1, "Red", "Yellow")))

In my example the Status Column is the parent but in yours, it would most likely be the one with Task names, please make sure to update the formula accordingly with the ANCESTORS function to refer to the Primary Column.

I hope that this can be of help.

Cheers!

Julio