Check all Child Checkboxes when Parent is Checked

Andy Lorance

Greetings -

I am looking for a formula that will automatically check the child rows associated with its parent on a Pivot sheet. Any help is greatly appreciated.

Thanks in advance!

Andy

Genevieve P.

Hi @Andy Lorance

@Andrée Starå 's initial solution of =PARENT() would be the way to do this to have the result in the same column that's being filled out by the Dynamic View user.

You could potentially apply a formula to the entire column, but it would be overwritten as soon as a manual change was made (the checkbox in the Parent row).

However, you could use a helper column to receive the initial checkbox, and here in your Job Complete column you could put a formula that looks into this helper. In this case you would reference the Parent row of that column:

=PARENT([Helper Column]@row)

Now, you'll need to adjust this so that if the row that the formula in is the parent, it just replicates the Helper Column next to it (without looking at the parent).

=IF(COUNT(CHILDREN([Primary Column]@row)) >=1, [Helper Column]@row, PARENT([Helper Column]@row))

This says, if the Count of Children associated with this row is 1 or more, then simply return what the checkbox looks like in the Helper column. If there are NO Children (meaning it is a Child), then return the Parent's status in the Helper column.

Does this make sense? I chose the Primary Column for the initial search for Children as I assume that there is content in the child rows for that column. You will need to replace the [Helper Column] with the title of whatever this column is.

Let me know if this works for you!

Cheers,

Genevieve

Andrée Starå

Hi @Andy Lorance

You can use the formula below in the child rows.

=PARENT()

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andy Lorance

Hi, Andrée - thanks for the reply! That get's me closer ... I'm hoping to use a column formula to limit the maintenance on the sheet. Here's the workflow: the user checks the box on the parent row in a dynamic view, then the child rows automatically check accordingly. Not sure how to accomplish both a column formula and allow user manipulation on the same target cell.

Thanks!

Andy

Andrée Starå

@Andy Lorance

Happy to help!

Unfortunately, the Column Formula is all or nothing so you can't use it and also be able to change the values manually. You'd need to use a so-called helper column.

Would that work?

Andy Lorance

Yes, a helper column would work ... I initially tried something along those lines, but got stuck.

Andy Lorance

Hello - checking back in for a solution on this one. Here's what I'm trying to achieve:

When any of the checkboxes on the blue highlighted row are checked, I'd like the below checkboxes to check as well. I have parent/child logic built based on other columns; I'm trying to use that here, but coming up with errors. The goal solution also needs to be low maintenance (column based formula or something thereof).

Genevieve P.

Hi @Andy Lorance

@Andrée Starå 's initial solution of =PARENT() would be the way to do this to have the result in the same column that's being filled out by the Dynamic View user.

You could potentially apply a formula to the entire column, but it would be overwritten as soon as a manual change was made (the checkbox in the Parent row).

However, you could use a helper column to receive the initial checkbox, and here in your Job Complete column you could put a formula that looks into this helper. In this case you would reference the Parent row of that column:

=PARENT([Helper Column]@row)

Now, you'll need to adjust this so that if the row that the formula in is the parent, it just replicates the Helper Column next to it (without looking at the parent).

=IF(COUNT(CHILDREN([Primary Column]@row)) >=1, [Helper Column]@row, PARENT([Helper Column]@row))

This says, if the Count of Children associated with this row is 1 or more, then simply return what the checkbox looks like in the Helper column. If there are NO Children (meaning it is a Child), then return the Parent's status in the Helper column.

Does this make sense? I chose the Primary Column for the initial search for Children as I assume that there is content in the child rows for that column. You will need to replace the [Helper Column] with the title of whatever this column is.

Let me know if this works for you!

Cheers,

Genevieve

Andy Lorance

This worked like a charm!!!!! Thanks a million!!!!

Genevieve P.

Hi @Andy Lorance

No problem at all! Happy to help.

Andrée Starå

@Andy Lorance

I saw that Genevieve answered already!

Let me know if I can help with anything else!

Best,

Andrée

Briana Montenegro

Hi! I am still having this issue, here is a screenshot of my sheet

Genevieve P.

Hi @Briana Montenegro

Try making your Helper Column a checkbox type as well.

You'll need to swap out the column names in the formula to reflect your own column names, like so:

=IF(COUNT(CHILDREN([Job/Scope/Activity]@row)) >=1, [Helper Column]@row, PARENT([Helper Column]@row))

Remember that you're pasting this formula into your "Done" column as a Column Formula. Then try checking the box in a Parent Row of the Helper Column to test and see if all the children auto-populate as you want them to.

If it's still not working, it would be helpful to know a bit more about what you're looking to do and what formula you have tried.

Cheers!

Genevieve

Suzanne Raphael

In the same neighborhood, but not quite the same details. I have a formula for a "Show row" checkbox column, based on whether any of 3 other checkbox columns are checked:

=IF(OR([Template 13, Full Scope]@row = 1, [Template 14, Limited Scope]@row = 1, [Template 15, 11k]@row = 1), 1, 0)

That works fine and I have a filter to show rows with based on "Show row" being checked. But I also want its CHILDREN to show. How do I accomplish "if my parent is checked, also check me" so that I create an "and" filter? And honestly why don't filters just give you the option to toggle/include children??

I have added a "Check the Children" helper column, but from there I'm not having any success. I also already have a Children column that I use for conditional formatting.

Genevieve P.

Hi @Suzanne Raphael

Try this:

=IF(COUNT(CHILDREN([Question type/#]@row)) > 0, IF(OR([Template 13, Full Scope]@row = 1, [Template 14, Limited Scope]@row = 1, [Template 15, 11k]@row = 1), 1, 0), PARENT())

You won't need to have two columns. The first part of the formula looks to see if the current row is a Parent or not. If it is, it looks for checkboxes across the row. If it's not (if it's a child row), then mirror the current Parent row checkbox.

Cheers,

Genevieve