Parent Child Numbering
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
Best Answer
-
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.
Answers
-
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.
-
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
-
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.
-
Got it
Thank you again!
-
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?
-
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.
-
Hmm. for the entire sheet or just for the row?
-
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.
-
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?
-
Have you checked the Activity Log to see how it compares?
-
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.
-
-
@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!
-
@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.
Help Article Resources
Categories
Check out the Formula Handbook template!