Formula to work with different levels of Hierarchies

spham5

Hi,

I'm currently trying to figure out a formula that would help with collecting results from a certain column and only when those results are all "true", can the Child row (of the same column) be marked as "true" (without affecting other parent/child/grandchild rows of the same column).

Here is an image of what I'm trying to get at. Basically a formula where the checkbox of column 2 for the Child row will automatically check itself once the grandchild rows indented below it have all of their boxes checked in column 2. However, the formula needs to only look at the grandchild rows of that specific child row and not go beyond it.

I've tried working with CHILDREN function, DESCENDANTS function, etc, but I have not figured out how to make it more "autonomous". What I mean by autonomous is a formula that could automatically check for the boxes in column 2 regardless of how many grandchild rows there may be underneath the child row. I thought it could be better than having to manually select the range of grandchild rows beneath the child row each time a new set of Parent/Child/Grandchild rows are created for my project.

Is such a formula possible on smartsheet as of currently?

Ipshita

Hello @spham5

If I understand your question correctly, what you are trying to achieve here is a formula in column 2 for Child row that if, all the Grandchild rows below the Child row are checked, then the Child row for column 2 automatically marks itself checked. In such a case, you will need to write the formula in column 2 for the Child rows which would be something like an IF statement,

=IF([Column2]@row = 1, 1, 0) but then, this won't be possible as the logic is not right since this column is also a checkbox for other Grandchild rows and those have to be manually checked so how will you differentiate which one should auto check and which one is manual. I will try to look at this issue from a different angle as well just need some time to test it out. Please correct me if I have understood your question incorrectly.

Thanks,

Ipshita

Ipshita

Hello @spham5

If I understand your question correctly, what you are trying to achieve here is a formula in column 2 for Child row that if, all the Grandchild rows below the Child row are checked, then the Child row for column 2 automatically marks itself checked. In such a case, you will need to write the formula in column 2 for the Child rows which would be something like an IF statement,

=IF([Column2]@row = 1, 1, 0) but then, this won't be possible as the logic is not right since this column is also a checkbox for other Grandchild rows and those have to be manually checked so how will you differentiate which one should auto check and which one is manual. I will try to look at this issue from a different angle as well just need some time to test it out. Please correct me if I have understood your question incorrectly.

Thanks,

Ipshita

spham5

Hi @Ipshita ,

Thank you so much for your time and response and yes, you understood correctly. Fortunately, I was given guidance on this question elsewhere recently and was able to figure out the solution to my problem. The formula I had used was:

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

This worked flawlessly for both the CHILD row and PARENT row as it helped automate the checking process.

Ipshita

Hi @spham5

That's wonderful, I was just testing out the logic other ways and glad it has already started working for you.

Cheers,

Ipshita