Parent Row Check Box Complete when all child rows are checked off

BESP10

Good afternoon

I am using this formula

=IF(COUNT(CHILDREN(Scope1)) = COUNTIF(CHILDREN(), 1), 1, 0) to try and have the parent row checked off when all the children are checked off but it is coming up with this error, any ideas on what I could be doing wrong?

Checkbox Error.png

Kelly Moore

Hi

This error is typically associated with a syntax error when trying to apply a column formula. Were you trying to do this? The error arises when one uses a specific row number or calls out an absolute reference. In your example, the formula references Scope1 vs Scope@row

I noticed in your screenshot that the column is locked, which suggests you are checking the boxes, including the children rows, by formula. If you needed to differentiate between the formula of parent rows and children rows, you could do this with an IF statement which first checks if the row is a Parent row or not, and convert the nested IF to a column formula. This formula could look something like:

=IF(COUNT(CHILDREN(Scope@row))>0, IF(COUNT(CHILDREN(Scope@row)) = COUNTIF(CHILDREN(), 1), 1, insert your formula for child [Comp.] rows ))) <you may need more parentheses depending on your child row formula and make sure any row references are @row references>

Does this solve the problem for you?

BESP10

HMM, this formula works when I drag the formula down the sheet

=IF(COUNT(CHILDREN(Scope1)) = COUNTIF(CHILDREN(), 1), 1, 0)

but when I try to make it a column formula I get this error message, I don't understand why it won't work as a column formula

Andrée Starå

Hi @BESP10

Try adding a simple formula like columnname@row and make it a column formula, and then edit the column formula to what you had previously.

Did that work?

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!

MrDramaFree

@KDM or @Mark Cronk , I'm running into some issues with the parent checkbox. In one instance, I have 4 children rows and 1 parent row, but the parent row is automatically adding a checkbox when only 3 of the children rows are checked and not waiting for the 4th row to be checked before checking the parent row. Any idea why this would happen or is there an error in my formula? Thanks for your help!

Here is the formula I'm using along with screenshot showing how parent is check even though not all children are checked. Goal is to only have Parent checked when all children are checked, otherwise leave unchecked.

=IF(COUNT(CHILDREN([Success/Win?]1)) = COUNTIF(CHILDREN(), 1), 1, 0)

Kelly Moore

Hi Shane,

Try comparing the count of the children to the Primary column - the primary column will always give you the total number of children.

=IF(COUNT(CHILDREN([Key Result]@row)) = COUNTIF(CHILDREN(), 1), 1, 0)

We encourage you to use @row in place of designated row numbers in your formulas. This will make your formulas more robust.

If your children checkboxes are checked using a formula, you can differentiate between the two formulas with an IF statement that calls out parent rows. If this is something you are interested in and need help with, let us know.

MrDramaFree

Thanks @KDM . This solved the issue and much easier formula. However, I'm now running into issues where I have parent rows that have no children and it's checking the box, so I'm having trouble editing the formula so if children are checked, check the parent but if there are no children, leave parent unchecked.

Kelly Moore

Will this work?

=IF(COUNT(CHILDREN([Key Result]@row))>0, IF(COUNTIF(COUNT(CHILDREN([Key Result]@row)) = COUNTIF(CHILDREN(), 1), 1)))

MrDramaFree

@KDM Unfortunately, it did not work and returned #INCORRECT ARGUMENT SET

Kelly Moore

I copied pasted my original reply incorrectly. I edited the formula.

MrDramaFree

@KDM It works if there are no children, but once I add a child to the parent, the same error populates in the parent cell.

Kelly Moore

grrr. That's my fault for rushing. sorry. If this one doesn't work, please show me a screenshot of your formula. On my test sheet I found I had a parenthesis out of place.

=IF((COUNT(CHILDREN([Key Result]@row))) > 0, IF(COUNT(CHILDREN([Key Result]@row)) = COUNTIFS(CHILDREN(), 1), 1))

MrDramaFree

@KDM That's it! It works perfectly! Thank you so much for the prompt support!

MrDramaFree

@KDM how do I prevent the formula from deleting once a dropdown color is selected? The formula works, but once I select an option from the dropdown, the formula disappears and so if I end up creating a child post dropdown selection, I have to re-input the formula.

Kelly Moore

Hey Shane

You will always overwrite a formula if you manually insert a result (ex. a color) in the same cell as a formula. Are you saying that sometimes you manually update a row and when you do, the formulas don't auto-fill down the column? If yes, this is because you're breaking the continuity of auto-fill with the manual insertion and your formula will always stop auto-filling down the column. Always.

Help me understand what you're trying to do and perhaps we can come up with a formula that wouldn't need a manual insertion- or at least not in the same column as the formula. Let me know how I can help.

Kelly

BESP10

Hi @kdm and @Andrée Starå , I am just getting back to trying to make this work. I am using this formula

=IF(COUNT(CHILDREN(Scope@row)) = COUNTIF(CHILDREN(), 1), 1, 0)

It is working when I check off all child rows under the parent row, however when I try and make it a column formula it automoatcally checks every row in my sheet.

Would you know how to fix this?

Thank you

Parent Row Formula.gif