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 parent-complete 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!