Formula ,Ancestors ,Parents and children

David Clunie

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

Genevieve P.

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

Paul Newcome

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.

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

Becky M.

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

Genevieve P.

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

Becky M.

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