How to identify child and sub-childs of a parent so I can add conditional formatting?

SJ1

Hello, I have several sub-childs and I am trying to add a column where I can for example say if a cell is Parent return parent, if child return child and if sub-child return sub-child?

I looked into several resources but there isn't any easy answer. If you can please help.

Jeff Reisman

@SJ1

You can create a helper column to count the number of ANCESTORS of a cell.

0 ancestors = parent

1 ancestor = child

2 ancestors = sub-child, etc.

The formula is:

=COUNT(ANCESTORS(ColumnName@row))

Make it a column formula and it will apply to every row and every new row. Set your conditional formatting based on the value result from the formula.

SJ1

Thanks Jeff! that partially worked. I had to use https://community.smartsheet.com/discussion/10461/using-hierarchy-formulas-conditional-formatting-to-color-code-rows-in-a-sheet. Appreciate your help.

alagator28

Thank you, @Jeff Reisman. Your formula worked perfectly for me.

Jeff Reisman

@alagator28 Glad to hear it!

So I have to ask: Are you an Alabama Gator? My wife went to Alabama, while I went to Florida (SEC Football Interfaith Marriage,) so our kids are definitely Ala-Gators! 🤣🐊

alagator28

@Jeff Reisman no, I'm not an Alabama Gator, just a similarity with my name. But hey, Go Gators! 🤣