Formula ,Ancestors ,Parents and children

Options

Hi all

Is there a way to do the following Numbering in projects for Levels to have a uniquie identifier?


1.00 as Ancestor

2.00 as Parent

2.01 as the first child

2.02 as Second child

........etc

3.00 as the second parent if found

3.01 as the first child

3.02 as Second child

........etc

1.00 as new Ancestor if new group started


Thanks



Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @David Clunie

    There is a quick way to simply identify if a row is an Ancestor, Parent, or Child, but what gets tricky is when you want to then identify the order of the rows based on hierarchy.

    For example, we could write a rule that says if the current row has 0 levels above it, then it's an Ancestor. If there is 1 level above, it's a Parent. Otherwise, if there are 2 or more levels above, it's a Child:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, "Ancestor", IF(COUNT(ANCESTORS([Primary Column]@row)) = 1, "Parent", "Child"))

    You can have these equate to numbers instead of the words:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, 1, IF(COUNT(ANCESTORS([Primary Column]@row)) = 1, 2, 3))

    However this would then have a set number assigned to each level, without it changing as new children are added. So in your image above, both the first and second parents would be assigned the number 2.


    Separately, Smartsheet has an auto-number column that you could include in your sheet to automatically number each row incrementally, but this would be based on when the row was created, versus the location or hierarchy in the sheet.

    As Paul mentioned, there may be other posts in the Community that have discussions on this (like this one), but depending on your specific process it may be easier to leverage the formula above to create Conditional Formatting rules for visual cues or to use in a Report instead of creating a complex formula.

    I hope this helps!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    It is possible. Try doing a search for "WBS" here in the Community. There should be a few solutions that you could adapt.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @David Clunie

    There is a quick way to simply identify if a row is an Ancestor, Parent, or Child, but what gets tricky is when you want to then identify the order of the rows based on hierarchy.

    For example, we could write a rule that says if the current row has 0 levels above it, then it's an Ancestor. If there is 1 level above, it's a Parent. Otherwise, if there are 2 or more levels above, it's a Child:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, "Ancestor", IF(COUNT(ANCESTORS([Primary Column]@row)) = 1, "Parent", "Child"))

    You can have these equate to numbers instead of the words:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, 1, IF(COUNT(ANCESTORS([Primary Column]@row)) = 1, 2, 3))

    However this would then have a set number assigned to each level, without it changing as new children are added. So in your image above, both the first and second parents would be assigned the number 2.


    Separately, Smartsheet has an auto-number column that you could include in your sheet to automatically number each row incrementally, but this would be based on when the row was created, versus the location or hierarchy in the sheet.

    As Paul mentioned, there may be other posts in the Community that have discussions on this (like this one), but depending on your specific process it may be easier to leverage the formula above to create Conditional Formatting rules for visual cues or to use in a Report instead of creating a complex formula.

    I hope this helps!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Becky M.
    Options

    @Genevieve P. I copied/pasted your formulas and it says "UNPARSEABLE" on both - what am I doing wrong?

  • Genevieve P.
    Options

    Hi @Becky M.

    Do you have a column in your sheet named "Primary Column"? If not, you'll want to change out the name in [these] to match a column name in your sheet. I hope that helps!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Becky M.
    Options

    @Genevieve P. Oh duh, thank you. Just getting started on this SmartSheet journey! I did figure out the individual column counts but like this better. Thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!