Formula regarding parent / child rows

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 Community Champion
    Answer ✓

    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 Community Champion
    edited 02/11/23

    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

  • 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 Community Champion
    Answer ✓

    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

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

  • 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 Community Champion

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

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group

    HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    Original Smartsheet Profile: @Sherry Fox

  • Bill in Ohio
    Bill in Ohio ✭✭✭✭✭

    @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.

    "Even my contingencies have contingencies."

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!