Formula regarding parent / child rows

Options

Hello,

I'm trying to create a formula to automatically populate a column based on if the row's primary column cell is a "parent" or "child."

The column I want to automate is titled "Level." If the primary column cell at that row is a "parent", I want the "Level" column cell at that row to automatically populate "1." If it's a "child", I want it to automatically populate "2."

Is there a formula for this?

Thanks!

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Kaitlyn Carroll

    Sorry, I missed you had replied. Try this

    =IF(OR(COUNT(ANCESTORS([your primary column]@row)) = 0, COUNT(CHILDREN([your primary column]@row)) > 0), 1, IF(COUNT(ANCESTORS([your primary column]@row)) > 0, 2))

    Insert the real name of your primary column if the open parentheses give you a circular error

    Does this work for you

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 02/11/23
    Options

    Hey @Kaitlyn Carroll

    This formula will indicate if a row is a Parent or Non-Parent

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

    If your sheet also has rows that are neither Parents or Children, it will also indicate these rows as a 2

    If this is a concern, you must add an additional condition

    =IF(AND(COUNT(ANCESTORS())=0, COUNT(CHILDREN())>0), 1, IF(AND(COUNT(ANCESTORS())>0, COUNT(CHILDREN())=0), 2))

    Will either of these work for you?

    Kelly

  • Kaitlyn Carroll
    Options

    Hi Kelly,

    Thank you for the formulas!

    The first formula would be great. However, as you stated, if it's neither a Parent or Child it's marked as "2" and that won't work.

    When I use the second formula you shared, it gives me the following errors:

    Any ideas? Also, I noticed the cell is blank when it's not a "Parent." I would need those "non-parent" rows to be "1" still. Is that possible?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Kaitlyn Carroll

    Sorry, I missed you had replied. Try this

    =IF(OR(COUNT(ANCESTORS([your primary column]@row)) = 0, COUNT(CHILDREN([your primary column]@row)) > 0), 1, IF(COUNT(ANCESTORS([your primary column]@row)) > 0, 2))

    Insert the real name of your primary column if the open parentheses give you a circular error

    Does this work for you

    Kelly

  • Kaitlyn Carroll
    Options

    That worked! I'm so excited!! Thank you so much!

  • sbrelage
    Options

    So I would like to use this formula to provide info at 4 levels of the hierachy, see example below. I've messed with this formula for the last 30 minutes and now have completely confused myself. Any ideas?


  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    I have a need for the same exact formula type with multiple level of hierarchy

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Bill in Ohio
    Options

    @Sherry Fox , this has worked for my needs:

    =IF(COUNT(ANCESTORS([PRIMARY]@row)) > 1, COUNT(ANCESTORS([PRIMARY]@row)), IF(COUNT(ANCESTORS([PRIMARY]@row)) = 1, 1, 0))

    Change the [PRIMARY] to your primary column. Let me know if this works for you. It's not a complex formula, but I was just looking for calculating the top level to zero, and then primary items to be level 1, secondary as 2, and continuing.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!