Helper Column Parents and Tasks with 0 Children

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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

Comments

  • 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 NewcomePaul 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.

  • edited 01/11/19

    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 WBrian W
    edited 01/11/19

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

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

  • Paul NewcomePaul 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 NewcomePaul Newcome ✭✭✭✭✭

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

  • Andrée Starå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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • This one worked! Thank you Thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Excellent! Happy to help. yes

Sign In or Register to comment.