Update parent row status based on the children status

I am using the formula below to update the status of a parent row based on the children's status, but when all statuses are blank, it is showing the parent as "Complete". I need it to show as "Not Started" if all children are blank.


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

Best Answer

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    I would create a helper column and do something like if(isblank((Status@row)),0,1) for each record

    then sum those children at the parent level. And if that total is 0, then Not Started.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!