Using an IF formula for Parents, Children rows

spham5

Hi all,

I'm currently trying to see if there's a formula that can be used to affect the parent row (row above the indented row) after a certain condition has been met within the children row (indented rows). Please see the image as a reference.

In this example I've set up, I'm trying to figure out if there's a formula that can help check the box on the CHILD row after all GRANDCHILD boxes below it have been checked off. I'm trying to figure out where the completion of boxes on the GRANDCHILD rows only affect the CHILD row above it and not the PARENT row or any of the other PARENT / CHILD / GRANDCHILD rows that are unrelated. I have yet to be able to come up with an IF formula for that process.

Is such a formula possible? I tried the DESCENDANTS / PARENT / CHILDREN functions to no avail.

For further information if necessary, in my actual project, the boxes in the GRANDCHILD rows have a formula that automatically check those boxes once certain conditions have been met. So I'm trying to figure out a formula that will then check the box in the CHILD row once all boxes in the GRANDCHILD row are checked off.

If such a formula is possible I would then want to expand it as my actual project, a PARENT row could have multiple CHILD rows beneath it and each CHILD row could have multiple GRANDCHILD rows beneath that. So I wanted to automate a way for those CHILD rows to be checked once their corresponding GRANDCHILD rows are checked, and once all corresponding CHILD rows are checked, the PARENT row will then be checked off.

Would anyone be able to help if they may know such a formula? Thank you in advance!!

SteyJ

Hello @spham5

Try using this

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

You are dividing the sum of the checkboxes which are checked (value of 1), by the total number of checkboxes for each parent.

The formula can be used for both the parent and child rows.

Hope this helps.

SteyJ

https://community.smartsheet.com/discussion/comment/393776#Comment_393776

Should be possible, what are the conditions you need to check for?

SteyJ

Hello @spham5

Try using this

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

You are dividing the sum of the checkboxes which are checked (value of 1), by the total number of checkboxes for each parent.

The formula can be used for both the parent and child rows.

Hope this helps.

spham5

@SteyJ , Thank you so much for that. The formula did indeed work.

Would you know if there's a way I can add that formula to another formula? Currently the column with the checkboxes already have a formula that automatically checks the grandchildren boxes (this current formula requires certain conditions to be met, conditions that are not present in the CHILD and PARENT rows).

As I understand, IF functions basically follows a IF (expression), (return value A, if not return value B). I tried adding your formula to the "return value B" portion of the formula already in that column, but now I get an error stating the divide by 0 and if I try an IFERROR approach, it gives an error of BLOCKED. I wonder if I may have to go about this another way.

SteyJ

https://community.smartsheet.com/discussion/comment/393776#Comment_393776

Should be possible, what are the conditions you need to check for?

spham5

@SteyJ ,

I was able to figure this one out! I retried the IFERROR function on the formula you provided and instead of calling for 1,0) at the end, I just called for ,0). Thank you so much again for your help, I couldn’t have gotten there without it! :)