Set checkboxes for all children - multi parent heirarchy

Frank Falco

I want to set a checkbox for all the children through multiple heirarchy based on the value in another column.

If you look at the attached examples, if the parent value for active is N/A then that row and all it's children should be checked, including any parents with children within that heirarchy.

I have three additional columns that count ancestors, count children and also the row number.

I need help with the code to set checkbox column

Richard Heath

Hi,

I think I've just undertaken what you've requested for my own needs.

When a user select "Yes" under Client Report, the following occurs;

1. Parent Row - In the cell adjacent the selected Yes I have - 
   • =IF([Client Report]1 = "Yes", 1)
2. In the Children Rows below, I have 
   • =COUNTIFS(PARENT(), 1) = COUNT(PARENT())

In your case, if a user selects N/A, the "Exclude" Checkbox will check all items below (Children).

See how you go. I'm sure others with more experience will have other ideas. 

Paul Newcome

=IF(COUNT(CHILDREN(Tasks@row)) > 0, IF(Active@row = "N/A", 1), PARENT())

 

Give something like this a try. First it looks to see if it is a parent row. If it IS a parent row, then it will look at the Active column. If that value is "N/A", it will check the box. If the row is NOT a parent row, then it will reflect the parent row's checkbox whether it be checked or unchecked.

 

This formula can go as is in all rows parent and children alike.

Paul Newcome

I would suggest something along the lines of what I posted below. The benefit of accounting for parent and children rows all in one formula is that you can adjust hierarchy as needed without having to worry about copy/pasting different formulas to different rows. You can also just use a basic PARENT() function to have a child row duplicate what is in its parent row of that same column. So if you run the criteria on the parent row, you can just pull the value from the parent row instead of having to run another set of criteria.

 

Something like this should work for you and simplify things if you need to adjust hierarchy.

 

=IF(COUNT(CHILDREN([Original Value]@row)) > 0, IF([Client Report]@row = "Yes", 1), PARENT())

Paul Newcome

My above comment is for Richard. The below is for the original post by Frank.

Mike Wilday

Do be aware that once a checkbox has a formula in it you can't check it off from a report. AND that once a person manually ticks or unticks a checkbox the formulas are overwritten. 

Just want you to be aware of some of the consequences of using a formula in column type that is frequently manually adjusted. 

Paul Newcome

Two very good points, Mike.

 

I think we should be safe in this particular instance though. The parent checkboxes are being driven by a different column, and the children are just reflecting the parents. It seems as if the checkbox column can be hidden/locked in this case to avoid editing, and since it is being driven by a different column, that column can be edited from the report.

 

Checkboxes can definitely be a risky automation though. People LOVE to change them even when they don't need to. Haha

Frank Falco

Basically you need to assess if this row is a parent or it's parent are N/A then set the Excl check box.

I have another column called Subs that has the code =COUNT(CHILDREN()) which is use for other calculations.

1) Subs@row <> 0 or >0 means that it is a parent row.

2) If that row is a parent the check it's parent is N/A or it is N/A, set Excl checkbox

3) If not a Parent, then check it is N/A or it's Parent is N/A, set Excl checkbox

=IF(Subs@row <> 0, IF(OR(PARENT(Active@row) = "N/A", Active@row = "N/A"), 1), IF(OR(Active@row = "N/A", PARENT()) = 1, 1, 0))

This covers all cases and works well. Then I locked and hid the Excl column, and used that for conditional formating and other calculations.

Thank you for everyone's help.

Paul Newcome

Happy to help! 

Will S.

Hi all,

Great forum for this, I'm still having some trouble with this for a sheet I'm trying to configure.

I thought this could be a conditional formatting, but I think this will need a formula.

I wanted the child rows to be checked if the parent value is Yes (need the QC event boxes checked) -

The "Yes" in NEeds Qual/Val section will need to check off the adjacent cell's check box in QC event, along with the child row checkboxes below. I wanted to get a column formula for the QC events, so that only when Yes in the Needs Qual/Val, it's able to automatically check these child events. Can someone get a formula for me? I tried Richards at the very top, but only half worked. I tried Paul's formula but it said unparsed, so I think I'm missing something.

Genevieve P.

Hi @Will S.

Try something like this:

=IF([Needs Qual/Val?]@row = "Yes", 1, IF(PARENT() = 1, 1))

This says that if the cell to the left is "Yes", then it checks the box. Also, if the PARENT of this current row is checked in the Checkbox column, then check the box.

Let me know if this works for you!

Cheers,

Genevieve