Move automation with multiple criteria

Lorraine Stevens

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?

Paul Newcome

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.

Lorraine Stevens

@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?

Paul Newcome

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)