Hide Parent Rows

Jerod Cramb

How do i hide or fliter out the parent rows in Grid View.  Im trying to link only the data to any other sheet another sheet.

J. Craig Williams

Jerod,

 

1. Create a new column, I named mine [Hierarchy Level].

2. In every cell, add this formula:

 

=COUNT(ANCESTORS())

 

3. The formula will return 0 for cells at the top of the ancestry chain.

Filter those out.

 

Ancestry Chain

family 1

Grandparent = 0

Parent = 1

Children = 2

 

family 2

Parent = 0

Children = 1

 

family 3

Childless = 0

 

Craig

 

 

Jerod Cramb

The children have different values  (4,5,6,7,8).  I may have asked the question in a confusing way.  Im looking to only have the children displayed.

J. Craig Williams

Jerod,

 

Yes, the children will have different values. To filter out the parents (only show the children), then filter out the values=0.

 

Put another way, filter to show values not 0.

 

My wording may have confused you.

 

Craig

J. Craig Williams

Or are you saying the results of 

 

=COUNT(ANCESTORS())

 

gives you a value of 8 for some cells?

That is a lot of indentation if so.

 

And if that is true, does that mean you are looking only for rows that have no children, regardless of how many ancestors it has?

 

Craig

Jerod Cramb

Yes I am looking for the rows with no children.  Does not mater what level they are at. 

J. Craig Williams

Jerod,

 

To determine any childless row, the formula is

 

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

 

where 1 = childless, 0 = parent

 

Craig

Jerod Cramb

When i paste =IF(COUNT(CHILDREN()) = 0, 1, 0)

 into the cell i get #CIRCULAR REFERNCE error.  Do you know what im doing wrong?

 

Jerod Cramb

When i paste =IF(COUNT(CHILDREN()) = 0, 1, 0)

 into the cell i get #CIRCULAR REFERNCE error.  Do you know what im doing wrong?

 

J. Craig Williams

Jerod,

 

I have never seen a circular reference for this formula.

Can you share it with me or post a screen shot?

 

Craig

Jerod Cramb

I deleted the column a made i need column to recreate the error but it worked this time. Thanks!