Automate a Parent based on a Child?

Katelynn.Hodgkins

Hello,

Is there a way to automate the value in a parent cell based on what's listed in the children cells? I have a sheet set up with a column to indicate if a milestone is complete or ongoing. Some rows I have this automatically changed based on the finish date, but some rows need to be confirmed by the task owner before changing to complete. I would like to have an automation that lists the parent task as complete if all its children are listed as complete. Is there a way to do that?

I did see this question was asked before, and there wasn't a way to do that, but that post was from 2018, so I was hoping that has changed.

Paul Newcome

We would also have to automate the other statuses through the formula if you want to use a formula to update for "Complete". What are your other statuses and what are the requirements for them?

Katelynn.Hodgkins

https://community.smartsheet.com/discussion/comment/302429#Comment_302429

I do have automation set up to change the value to complete if the finish date is in the past and the column indicating confirmation is needed isn't checked off. But if we don't have confirmation on one of the children (and it's required), I don't want the parent to be marked complete.

Paul Newcome

My apologies. I didn't mean an automation. I meant building it into the formula. Once a cell value is changed, the formula is erased. So if you have a formula to determine "Complete" and someone changes it to "In Progress", that formula is now gone.

Katelynn.Hodgkins

Right, so I want the ability to manually change the status to complete or to any of the other status I have in the drop down if necessary, so that's why I was leaning away from a formula. This is also going to be in a template for colleagues, some of which are just learning smartsheet, so I want to keep it as user friendly as possible.

Paul Newcome

You may be able to make it work using an Automation to output the desired result based on a helper column. The helper column (which can be hidden) would have a formula to (for example) check a box on the parent row when it meets the criteria you are wanting.