Helper Column Parents and Tasks with 0 Children

dblasco

I am using this formula in a helper column to identify which of my rows are "parents" however I would also like to Identify Rows that are Projects or also "Parents", but have 0 children " so that I can tag in conditional formatting along with my Parent Rows that have children.  Can you help me create a formula that can do this?

=IF(COUNT(CHILDREN([Project Name]@row)) > 0, "Parent", "")

 

 

Nic Larsen

We use a similar helper column for conditional formatting with this formula below to show what level it is in the project. Then we use conditional formatting off the level to color code rows. It will display level numbers (1, 2, 3, etc) depending on how many levels you drill down to. 

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

 

 

Paul Newcome

Try using a COUNTIFS and a PARENT functions together. If the resulting number is equal to the MAX, then it is the lowest level of hierarchy. If it is less than the MAX then it is not the lowest level which in turn means that it is a "parent row without a child" so to speak.

dblasco

Thank you, this one is still only returning a number for a Parent Row with Children, and not showing anything on the Tasks with 0 children...  

In this example I placed a check mark next to all of the other rows I want to Return a result for in the Parent column. Not sure if this helps clarify what I need or not.  I'm also new to smartsheet.  I'm not sure how to share my actual sheet.

any help is appreciated.

https://app.smartsheet.com/sheets/JcfPrP7xWv7qCW38VWjVvjgJQhQv7P4wWH7cr2V1

Brian W

It should work if you count for Ancestors instead of Children.

=IF(COUNT(ANCESTORS()) > 0, "", "Parent")

Paul Newcome

=IF(COUNT(PARENT()) < MAX([Parent Count Column Name]:[Parent Count Column Name], "Parent")

Try something like this. Work backwards. Instead of counting the children rows, count the parent rows. If it is equal to the highest number, then it is the lowest level of hierarchy.

Paul Newcome

Thanks Brian. I meant ANCESTORS to count how many parents the row has... Ugh. It's Friday...

Andrée Starå

Hi,

You can use the below formula with conditional formatting if you choose to not include any levels.

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

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

dblasco

This one worked! Thank you Thank you!

Paul Newcome

Excellent! Happy to help.