Parent Row Update Based on Children

Options
Nikki. A
Nikki. A ✭✭
edited 04/22/22 in Formulas and Functions

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")))

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 04/22/22
    Options

    @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".

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Nikki. A
    Options

    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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!