Automate Parent Row formula and keep children formula in place

TJordan ✭✭
edited 11/08/23 in Formulas and Functions

I'm trying to figure out the best way to automate a formula specific to the parent tasks and keep the existing children formula in place. The goal is for the user to be able to add more parent/child tasks without having to insert the formulas manually.

The parent formula in use:

=IF(COUNTIF(CHILDREN(), "Red") >= 1, "Red", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Yellow") >= 1, "Yellow", "Green"))))

The Child formula in use:

=IF(Status@row = "In Progress", "Green", IF(Status@row = "BLOCKED", "Red", IF(Status@row = "Complete", "Blue", IF(Status@row = "At Risk", "Yellow", ""))))

I suspect I may need to add a new column and possibly use an IF statement for Parent rows but having trouble visualizing what that formula would actually look like.

Any suggestions?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!