How to create automation that only works on parent rows.
I am trying to create an automation so that when the parent row is marked as complete, then the parent row and all of the children move to another sheet. I am just not able to figure out how to set the automation so that it ONLY moves if the parent row is selected and not the children rows. I did a formula so that if the parent row is selected, then it selects all of the children rows. But is there a way to do a helper column so that it only triggers the automation once a parent row is selected?
Best Answers

=IF(AND(Complete@row = "Complete", COUNTIFS(CHILDREN(Complete@row), @cell = "Complete") > 0), 1, IF(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, 1, ""))
This will check the parent row and all the children if the parent row is marked complete but will not mark if the parent row is not marked complete

=IF(AND(Complete@row = "Complete", COUNTIF(CHILDREN(Complete@row), >0) >= 0), 1, IF(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, 1, ""))

Just in case someone finds this post later looking for a way to only check the box if all parent and children are marked complete. You can do the below.
If you add an additional helper column, I called it Parent you can use the below formula in the Parent column.
=IF(PARENT([Task Name]@row) = "", [Task Name]@row, PARENT([Task Name]@row))
Then in your checkbox column use
=IF(AND(Complete@row = "Complete", COUNTIFS(CHILDREN(Complete@row), @cell = "Complete") = COUNT(CHILDREN())), 1, IF(AND(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, COUNTIFS(Parent:Parent, Parent@row, Complete:Complete, "Complete") = COUNT(Parent:Parent, Parent@row)), 1, 0))
Answers

You can create a helper column that determines if the Primary Column is a Parent with an IF.
=IF([Primary Column]@row = PARENT(), "Parent", "Child")
Then either build on this formula, or reference the formula to determine how to handle Parent, Child cells.

@Mr. Chris , thank you for your response! When I do this, it doesn't seem to pick it up correctly. For instance, there is a child row labeled as a parent row, and some parent rows labeled as children rows (blue are parent rows and white are child rows)

@Mr. Chris it is seeming that if something is marked as In progress, it is calling it a child row, and if it is blank, it counts it as a parent row.

=IF(AND(Complete@row = "Complete", COUNTIFS(CHILDREN(Complete@row), @cell = "Complete") > 0), 1, IF(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, 1, ""))
This will check the parent row and all the children if the parent row is marked complete but will not mark if the parent row is not marked complete

@Hollie Green amazing, thank you so much! This did the trick.

@Hollie Green , I may have spoke too soon! This worked for all of my existing data, but when I added new info to test the automation, and marked the parent row to complete, it only checked all of the child rows but not the parent row as well.

It's because none of your child rows are marked as complete. Let me see what I can do if they child rows aren't marked complete to get it to still work.

@Hollie Green ahh okay that makes sense. Thank you, that would be great!

=IF(AND(Complete@row = "Complete", COUNTIF(CHILDREN(Complete@row), >0) >= 0), 1, IF(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, 1, ""))

@Hollie Green this is perfect, thank you!

Just in case someone finds this post later looking for a way to only check the box if all parent and children are marked complete. You can do the below.
If you add an additional helper column, I called it Parent you can use the below formula in the Parent column.
=IF(PARENT([Task Name]@row) = "", [Task Name]@row, PARENT([Task Name]@row))
Then in your checkbox column use
=IF(AND(Complete@row = "Complete", COUNTIFS(CHILDREN(Complete@row), @cell = "Complete") = COUNT(CHILDREN())), 1, IF(AND(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, COUNTIFS(Parent:Parent, Parent@row, Complete:Complete, "Complete") = COUNT(Parent:Parent, Parent@row)), 1, 0))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!