Move automation with multiple criteria
Hello!
I have a 'Move Completed' automation set on a sheet that works great to move the completed parent (with their child tasks) to a Completed Tracker sheet. I am utilizing a helper hierarchy column which I've included as the filter condition in the automation - to move if the helper hierarchy is '0' and its status is complete. However, I've still been searching for a way to ensure that the move does not happen if any child tasks are incomplete. Even if the parent is 'complete', the entire hierarchy should not move if one child is incomplete. I've found some formulas but nothing is working quite right. Does anyone have suggestions?
Answers
-
You could incorporate a checkbox column that looks at the child rows and only checks when a count of "Incomplete" is equal to zero and use that as the trigger.
-
@Paul Newcome That sounds great. I have multiple statuses that are different phases of progress (i.e.: with design, pending review, etc.). Is there a way to create a count that works the other way to count just the 'complete' status for all children. That would be equal to 1, correct?
-
If you want to count how many are "Complete" then you would write out a COUNTIFS that counts how many CHILDREN([Status Column Name]@row) equals "Complete" then compare it to the total COUNT of CHILDREN([Task Name Column]@row). If those two numbers equal each other then all are complete and you can check the box.
=IF(COUNTIFS(CHILDREN([Status Column]@row), @cell = "Complete") = COUNT(CHILDREN([Task Name Column]@row)), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!