Formula to check a box or change status of a parent to complete when all the children are complete
Hello. I would like to have a parent status automatically change to Complete when all of the children rows have a status of Complete. I created a couple helper columns to accomplish this and then was planning to use automation for "when the parentcomplete box is checked, change the status to Complete". However, something is off. The Helper for Parent box checks no matter the status on the most indented child row (see row 3 and 6). Part of it seems to work (see lines 5 and 6 in example 2). Any ideas on a formula? Or is there an easier way to accomplish this?
The Helper for Complete formula is =IF(Status@row = "complete", 1, 0)
The Helper for Parent Complete formula is =IF(COUNTIFS(CHILDREN([Helper Column for Complete Status]@row), 0) = 0, 1, 0)
Best Answer

Hey @Terri1019
You can skip one of your helper columns and go right to the Parent formula  "Helper for Parent Complete":
=IF(COUNT(CHILDREN(Task@row)) >0, IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), 1, 0)
This first checks to see if the current row has a Child row or not. If it does (so if the count of children is greater than 0), then it moves on to the rest of the formula.
The second half of the formula counts how many child rows there are for this current parent based on text in the Task column. Then if the number of child rows is the same as the number of child rows that have "Complete" in the status column (meaning all child rows are complete), then it checks the box!
Let me know if this makes sense and works for you.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

Hey @Terri1019
You can skip one of your helper columns and go right to the Parent formula  "Helper for Parent Complete":
=IF(COUNT(CHILDREN(Task@row)) >0, IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), 1, 0)
This first checks to see if the current row has a Child row or not. If it does (so if the count of children is greater than 0), then it moves on to the rest of the formula.
The second half of the formula counts how many child rows there are for this current parent based on text in the Task column. Then if the number of child rows is the same as the number of child rows that have "Complete" in the status column (meaning all child rows are complete), then it checks the box!
Let me know if this makes sense and works for you.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thank you so much Genevieve! That is exactly what I needed! I appreciate you taking the time to help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!