Helper Column Parents and Tasks with 0 Children

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", "")

Example Parent 0 children.jpg

Find more posts tagged with

Education

Formulas

Project Management

Comments

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

example of my sheet.jpg

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…