How to automatically check a parent row when children are checked?

User251

Hi,

I have 3 level parent-children rows. I also have a column with a checkbox. Is there a formula or automation that would check the parent row if all children are checked?

Michael Culley

I would do something like this on your parent checkboxes:

=IFERROR(IF(COUNTIF(CHILDREN([checkbox column]@row), 1) / COUNT(CHILDREN([checkbox column]@row)) = 1, 1, 0), 0)

Michael Culley

You forgot some parentheses.

There should be one after [Column3]@row)

Try copying this one below:

=IFERROR(IF(COUNTIF(CHILDREN([Column3]@row), 1) / COUNT(CHILDREN([Column3]@row)) = 1, 1, 0), 0)

Michael Culley

I would do something like this on your parent checkboxes:

=IFERROR(IF(COUNTIF(CHILDREN([checkbox column]@row), 1) / COUNT(CHILDREN([checkbox column]@row)) = 1, 1, 0), 0)

User251

Thank you @Michael Culley for your answer. Can you please translate it 😊? I'm not sure I understand the logic.

User251

Oh! @Michael Culley I understood the logic.. that's very smart!! thank you!

Michael Culley

It's just counting how many of the children are checked (checked is equal to the number 1), then it divides that by the total number of children, and if the result = 1, then it will produce a 1 (a check).

User251

@Michael Culley, it is not possible to add a formula to a checkbox column...Should I add a second column that either shows a 1 or a 0? How will the parent checkbox be checked in that case?

Michael Culley

@User251

It is possible to add formula to checkbox column. Just right click the column, edit column properties, and uncheck the option to restrict to checkbox use only.

User251

@Michael Culley Thank you! I added the formula to the column, but I received the error #UNPARSEABLE. I also tried modifying the formula and using Children (), but I got the error #Blocked. What am I doing wrong?

Michael Culley

You forgot some parentheses.

There should be one after [Column3]@row)

Try copying this one below:

=IFERROR(IF(COUNTIF(CHILDREN([Column3]@row), 1) / COUNT(CHILDREN([Column3]@row)) = 1, 1, 0), 0)

User251

I@Michael Culley It worked! Thank you Thank you so so much!!! I really needed this feature to be added to a huge data sheet. Thanks again!

Parkster

@Michael Culley The formula works perfectly when I put it in the cell, but once I convert it to column formula, I can no longer check any of the boxes. Any ideas why?

Michael Culley

@Parkster When you convert it into a column formula, it won't allow you to do anything manually to the column at all. You would just have to copy the formula and paste it into each parent cell in that column. If for some reason that isn't feasible we'd have to rethink how we want to create this formula.