Identifying mid-hierarchy rows

KOwsley

I have a column of checkboxes and for each row I'm trying to identify if it is in the middle of the hierarchy (i.e. has at least one parent and at least one child). I'm trying to use this formula, but I keep getting a Circular Reference error. I can't logically figure out how it is a circular reference. If I remove the AND statement, it works just fine.

Also, it seems to work if I do it for just one row, but the error pops up when I convert it to a Column Formula.

=IF(AND(COUNT(PARENT()) > 0, COUNT(CHILDREN()) > 0), 1, 0)

What am I missing? I can set up a column to check for Parents, and a column to check for Children, and then a column to check both of those checks, but that feels really inefficient.

Paul Newcome

It becomes a circular reference because the parent is counting the child and the child is counting the parent. Try referencing another column that will always have data in it but is not the column the formula is in, and try counting ANCESTORS instead of PARENT.

=IF(AND(COUNT(ANCESTORS([Column Name]@row)) > 0, COUNT(CHILDREN([Column Name]@row)) > 0), 1)

Paul Newcome

It becomes a circular reference because the parent is counting the child and the child is counting the parent. Try referencing another column that will always have data in it but is not the column the formula is in, and try counting ANCESTORS instead of PARENT.

=IF(AND(COUNT(ANCESTORS([Column Name]@row)) > 0, COUNT(CHILDREN([Column Name]@row)) > 0), 1)

KOwsley

Thanks so much. That is really helpful.

Paul Newcome

Happy to help. 👍️