Check off a parent row when one or the other checkboxes in a child row is checked
I have a sheet with parent rows that roll up a set of child rows. The child rows have 2 columns that are not applicable or complete...once ALL the child rows have either Not Applicable OR Complete, I'd like to check of Complete in the parent row. I can get my formula to check off the parent if all of the child rows are completed in a single column, but how do I mix and match between columns? I'm including a screen shot of the sheet.
Thanks for any help anyone can provide
Best Answer
-
Hey @WFeagin
This will work assuming only Complete or Not Applicable is checked for each row. It checks the Complete box once the number of checks in the children rows reaches the number of children rows and only counts it if one of the two options are checked.
=IF(COUNT(CHILDREN(Primary@row)) = COUNTIFS(CHILDREN([Not Applicable]@row), =1, CHILDREN(Complete@row), =0) + COUNTIFS(CHILDREN([Not Applicable]@row), =0, CHILDREN(Complete@row), =1), 1, 0)
Answers
-
Hey @WFeagin
This will work assuming only Complete or Not Applicable is checked for each row. It checks the Complete box once the number of checks in the children rows reaches the number of children rows and only counts it if one of the two options are checked.
=IF(COUNT(CHILDREN(Primary@row)) = COUNTIFS(CHILDREN([Not Applicable]@row), =1, CHILDREN(Complete@row), =0) + COUNTIFS(CHILDREN([Not Applicable]@row), =0, CHILDREN(Complete@row), =1), 1, 0)
-
Thanks so much @Devin Lee! For it to work for me, I had to alter it slightly to:
=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN([Not Applicable]@row), =1, CHILDREN(Complete@row), =0) + COUNTIFS(CHILDREN([Not Applicable]@row), =0, CHILDREN(Complete@row), =1), 1, 0)
When I tried it with IF(COUNT(CHILDREN(Primary@row)) I got an unparseable error. The change also allowed me to paste the formula into each parent row without having to edit it based on row location. It works fantastically, butt ime for me to practice more!
Again, thanks so much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!