Parent Child formula for Checkboxes

Hello,

I am working on a checkbox column formula for a sheet that will function as a roll up but will not automatically check the lowest children.

Currently I have this:

=IF(COUNTIFS(CHILDREN(), 1) = COUNT(CHILDREN(Tasks@row)), 1)

That formula works, but you have to manually add it to each parent row. I would rather have something that I can set as a column formula instead if it's possible. Anyone able to help expand the above to what I am looking for?


Thanks!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Mross0878

    If you set your column to be a column formula, then you won't be able to manually add data to any of the cells (as they will be locked to the formula.

    You could put that same formula in a new, separate column, perhaps with an addition to make it blank if it's a child cell:

    =IF(COUNT(ANCESTORS(Tasks@row)) > 0, "-", IF(COUNTIFS(CHILDREN([Checkbox Column]@row), 1) = COUNT(CHILDREN(Tasks@row)), 1))

    Note that you'd need to reference your current column in your Count-Children part of the function. Does that make sense?

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Mross0878

    If you set your column to be a column formula, then you won't be able to manually add data to any of the cells (as they will be locked to the formula.

    You could put that same formula in a new, separate column, perhaps with an addition to make it blank if it's a child cell:

    =IF(COUNT(ANCESTORS(Tasks@row)) > 0, "-", IF(COUNTIFS(CHILDREN([Checkbox Column]@row), 1) = COUNT(CHILDREN(Tasks@row)), 1))

    Note that you'd need to reference your current column in your Count-Children part of the function. Does that make sense?

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!