Parent vs child flag

tgilles

Hello,

I'm wondering whether it is possible or not to write a formula to indicate whether a certain cell is a parent or a child. The sheet I'm working with only has 2 levels, and I'd like to create a column that is able to delineate the two.

My initial thought is something along the lines of =IF([Cell 1] = CHILDREN(), 1, 0) but this obviously returns an Invalid Operation error.

Is a formula like this even possible?

Thanks!

Angela Ryer

I personally create a column and use the formula =COUNT(ANCESTORS())

This will return a 1 or 2 or 3 etc (based on what level it is) 1 = parent and so forth.

I then use conditional formatting to state if this newly created Column (Ancestor) has a 1, then highlight entire row blue.

If 2, then highlight light blue. --- I can then hide the column, and know light blue rows are my children rows.

• From There, if you wanted to use a Flag - you could enter a formula into a Flag Column
• =IF(Ancestors@row = 1, 1, 0) then it will check the flag if Ancestor is 1 (which is the parent)

Paul Newcome

I use a setup similar to @Angela Ryer where I count the Ancestors.

I also suggest this even if you only have the two levels. Here is an example of where counting child rows may not work...

Task A

Sub 1

Sub 2

Task B

Task C

Sub 3

Sub 4

In the above, Task B does not have any Child Rows, but it is still the same hierarchy as the other Task Rows. If you were to base it off of children vs no children, scenarios like this may skew things a bit for you.

If you used something along the lines of

=COUNT(ANCESTORS())

All Task Rows would have a 0 (zero) and all sub-task rows would be a 1.

Angela Ryer

I personally create a column and use the formula =COUNT(ANCESTORS())

This will return a 1 or 2 or 3 etc (based on what level it is) 1 = parent and so forth.

I then use conditional formatting to state if this newly created Column (Ancestor) has a 1, then highlight entire row blue.

If 2, then highlight light blue. --- I can then hide the column, and know light blue rows are my children rows.

• From There, if you wanted to use a Flag - you could enter a formula into a Flag Column
• =IF(Ancestors@row = 1, 1, 0) then it will check the flag if Ancestor is 1 (which is the parent)

Paul Newcome

I use a setup similar to @Angela Ryer where I count the Ancestors.

I also suggest this even if you only have the two levels. Here is an example of where counting child rows may not work...

Task A

Sub 1

Sub 2

Task B

Task C

Sub 3

Sub 4

In the above, Task B does not have any Child Rows, but it is still the same hierarchy as the other Task Rows. If you were to base it off of children vs no children, scenarios like this may skew things a bit for you.

If you used something along the lines of

=COUNT(ANCESTORS())

All Task Rows would have a 0 (zero) and all sub-task rows would be a 1.