How to automatically check a parent row when children are checked?
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?
Best Answers
-
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)
-
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)
Answers
-
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)
-
Thank you @Michael Culley for your answer. Can you please translate it 😊? I'm not sure I understand the logic.
-
Oh! @Michael Culley I understood the logic.. that's very smart!! thank you!
-
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).
-
@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?
-
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.
-
@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?
-
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)
-
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!
-
@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?
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.6K Get Help
- 435 Global Discussions
- 152 Industry Talk
- 495 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am working in a separate sheet for calculating metrics. I want to count the number of items in a referenced date range in another sheet that meet certain year criteria. For example, I want to find t…
- I have a column that is a contact list where a single or multiple staff members can be selected to be scheduled. I'm trying to create a formula that counts how many times each individual's names are l…
- I have two columns I am using for this formula: "Date Received" and "Days Duration (Received to Listed)". I want the formula below to look at any items in the Date Received column between 1/1/25 and 1…