Update Parent Row Status based on Status of Child, GrandChild rows

jess_roberts

I've got a lot of helper columns already, in trying to track an enterprise level project.

I've got a status column.

I've got a progress column that is weighted based on the status columns.

I'd like to automatically update the ancestor and parent rows based on the status of the child rows below it.

So, if someone moves a task to In Progress, the ancestor rows should update to in progress.

I've tried writing a formula for if the progress is less than 100% but greater than zero.

I've tried writing every formula and googled everything I can think of. It's just not a simple formula and I'm ready to give up and just tell everyone their going to have to manually update every parent row if they make a change. This will negatively affect our adoption of Smartsheet.

Any suggestions would be appreciated.

Paul Newcome

Are you able to provide a screenshot for reference?

I am thinking a nested if should work.

=IF(COUNTIFS(CHILDREN(), @cell = "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), @cell = "In Progress")>= 1, "In Progress", "Not Started"))

jess_roberts

Additionally if 100% of the child rows are in "Completed" status or the progress of 100% of the children rows are at 100%, I want the parent row's status to update to "Completed".

I've added additional helper columns to see if I could write automations or workflows based on that column, but I can't get the formulas correct.

Paul Newcome

Are you able to provide a screenshot for reference?

I am thinking a nested if should work.

=IF(COUNTIFS(CHILDREN(), @cell = "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), @cell = "In Progress")>= 1, "In Progress", "Not Started"))

jess_roberts

@Paul Newcome thanks for the suggestion. It sort of worked, but, when I put the formula into the status column, I lost the ability to update the status of the children tickets. I think I'm just going to have to stay on top of everyone to update the parent rows.

Thanks!

Jess

Paul Newcome

Yes. Applying a column formula will remove the ability to manually update the child rows, but you have 3 options...

Manually put the formula in each parent row.

Develop a formula that can also automatically calculate the child rows and nest it into the column formula.

Use a helper column to house the formula and manually fill in the child rows in the existing column.