Parent Row Update Based on Children

Nikki. A · 04/22/22

I want to update the parent row of status based on the children.

If all are completed, then completed

If any are in progress, then in progress

if none are started then not started

The below formula generally works but it has couple of issues:

1) When I convert it to the column formula, then I cannot change the children rows manually.

2) It automatically sets the children rows that don't have any children to Completed

The idea is to update the children rows manually and then have the parent row reflect taht

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Complete", IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())), "Not Started", "In Progress")))

Jeff Reisman · 04/22/22

@Nikki. A

Your best bet here would be to add a "ParentStatus" column specifically for the parent rows. Put the below formula in that column and make it a column formula. Unless your child rows have child rows, the ParentStatus column will be blank on the child rows.

As far your formula goes, it looks pretty good. Since we're moving this to its own column, we need to specifically call out the Status column everywhere you use "CHILDREN()" to evaluate the child status values. The second criteria in the AND in the second IF statement is what's causing rows with no children to be set to "Complete." Remove that criteria and the AND. That last part for 'Not Started' looks ok:

=IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN()), "Complete", IF(OR(COUNTIF(CHILDREN(Status@row), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), "") = COUNT(CHILDREN())), "Not Started", IF(COUNT(CHILDREN(Status@row)) = 0, "", "In Progress"))))

Let's review the logic in English:

If any child rows have a status of In Progress, set this value to "In Progress"; otherwise, if the number of child rows with status of Complete is equal to the number of child rows, set this value to "Complete"; otherwise, if the number of child rows with a status of Not Started equals the number of child rows, OR if the number of child rows with a status of Not Started plus the number of child rows with a blank status equals the number of child rows, set this value to "Not Started"; otherwise, if the number of child rows is equal to 0, leave this value blank; otherwise, set this value to "In Progress".

Nikki. A · 04/22/22

Thank you very much, this won't' work for me. With the # of helper columns really getting out of hand..

I wish Smartsheet had a feature that would allow parent row to be automatically updated based on children rows

Jeff Reisman · 04/22/22

https://community.smartsheet.com/discussion/comment/327105#Comment_327105

You can do it in the same Status column as the child rows, you just won't be able to make it a column formula. There's just no way to have a formula column but still be able to manually change values, that would defeat the purpose of column formulas. So it's a trade off - either put the formula in its own column, or live with it not being a column formula.

Feel free to submit a product enhancement here:

Smartsheet Product Enhancement Requests

Parent Row Update Based on Children

Answers

Help Article Resources

Categories