# 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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭
Options

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

• Employee
Options

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