Another automate RYG balls for parent based on child

edited 12/09/19 in Formulas and Functions


I have a project plan with multiple levels of children. Currently, the RYG automation is as follows:


=IF(Status@row = "Done", "Gray", IF([Due Date]@row > TODAY(+7), "Green", IF(AND([Due Date]@row >= TODAY(+3), [Due Date]@row < TODAY(+7)), "Yellow", IF([Due Date]@row < TODAY(+3), "Red"))))


What I want to do is create a formula that identifies children and applies the formula above to them. Then, for higher levels of parents, it should be red if there is a red, yellow is there is a yellow but not red and green if there is no red or yellow, so that the parents inherit the "worst" level of the children.


I'm not really sure where to start with this so any help is appreciated.


All the best




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!