Parent Child Numbering

Detrie Zacharias

With A LOT of help from the community I was able put this together but, I think there must be a better way.

The last column is what I am trying to get to.

• Auto-generated Sequential number in this format T-00001 when Ancestors = 0
• Auto-Generated Sequential number in this format T-00001.1, T-00001.2 (where T-00001 is the Ultimate Parent) when Ancestors are NOT 0.

I have temporarily published it HERE with column comments

Paul Newcome

@Stavros_McGillicuddy

HERE is a published version of a quick two-level solution. If you need additional levels of hierarchy, let me know. I have another solution (very similar, but not quite the same) that currently extends out to six levels but uses a pattern than can be replicated to account for many more levels.

Paul Newcome

@Stavros_McGillicuddy

HERE is a published version of a quick two-level solution. If you need additional levels of hierarchy, let me know. I have another solution (very similar, but not quite the same) that currently extends out to six levels but uses a pattern than can be replicated to account for many more levels.

Detrie Zacharias

Hi Paul.. Happy Holidays!

This is exactly what I was looking for. I knew there had to be a more elegant solution.

I think by changing the column sort order, the WBS numbers duplicated but I cant seem to replicate it so I will keep an eye on it. Thank you so much for your help

Paul Newcome

Happy to help! 👍️

Please don't forget to mark as "Accepted Answer" so that others looking for a similar solution know that one has found here.

As for the duplication... Sorting the rows may cause it to temporarily duplicate, but it should only be temporary. If there si eve an issue, simply go back to the top row and make sure the formulas indicate

[Column Name]$1:[Column Name]@row

Then dragfill back down. The presented solution is built to look from the top to the bottom of the sheet and count in that order to establish how many child rows etc are present.

Detrie Zacharias

Got it

Thank you again!

Detrie Zacharias

Hi again,

I am an experiencing a problem with every Modified and Modified By values on my sheet are updating several times a day to the same person and time.

It seems to happen at the time a new record is created

Can this formula be causing it?

Paul Newcome

Yes. If the formula changes any data in a row, then the Modified columns will update to the date and time of as well as the user who initiated the change.

Detrie Zacharias

Hmm. for the entire sheet or just for the row?

Paul Newcome

Any row that has a change. So if a formula on row 3 is referencing row 2 and I change row 2, then row 3 will also show an updated date/time/person for last modified because I am the one that initiated a change on row 3 even though I only directly edited row 2.

Detrie Zacharias

Ok... That's what I expected. The rows in the group would be updated

My issue is that the values change on the entire sheet. All records have the same Modified By and Modified values.

This is not expected behavior from these formulas is it?

Paul Newcome

Have you checked the Activity Log to see how it compares?

Detrie Zacharias

It looks like the Modified and Modified By fields update every time a new record is created. The fields are updated with the created and created by values.

TsmithFastSigns

@Paul Newcome These formulas aren't working for me. Any idea why?

Thanks,

Terry

Paul Newcome

@TsmithFastSigns

Which formulas and exactly how are you using them?

Pelzer516

@Paul Newcome Is there any way to accomplish this same result but using column formulas for column one and two? I was able to get this all to work correctly, but I am always adding new rows for sub tasks and then have to unhide these helper rows and recopy the formulas into the new row (which sort of distracts from the automation). Thanks in advance!

Paul Newcome

@Pelzer516 There is a more updated version of a WBS solution that incorporates column formulas (mine was put together prior to column formulas being available). I believe there is also a link in that thread to a template from the solution center.

https://community.smartsheet.com/discussion/77827/work-breakdown-structure-wbs-column-formula-configuration