WBS with a 3rd level and using letters
![Melissa Torrez](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I am trying to build upon @Toufong Vang genius approach pasted below, however I am trying to solve for a 3rd level and using a. b. c....
I would like to add to the formula to build this 3rd level of the WBS (pictured below):
I need the sub-steps to show the full ID, such as
WS1.1.3.a
WS1.1.3.b, etc.
I. Create an auto-number column and ensure that it contains only numbers. The autonumber is going to be used as a "serial" number for the row. This helps to determine which row(s) came before any row.
II. Create the following helper columns in the following order.
• Parent_Serial (finds the parent of a row if there is one and returns the parent's serial number)
=IF(PARENT(AutoNum@row) <> 0, PARENT(AutoNum@row), 0)
• Parents_Count (counts the number of parents that were created before the current one)
=IF([Parent_Serial]@row = 0, COUNTIFS([Parent_Serial]:[Parent_Serial], @cell = 0, AutoNum:AutoNum, @cell < AutoNum@row) + 1, "")
• Child_Count (counts the children that were created before the current one)
=IF([Parent_Serial]@row <> 0, COUNTIFS([Parent_Serial]:[Parent_Serial], @cell = [Parent_Serial]@row, AutoNum:AutoNum, @cell < AutoNum@row) + 1, "")
• ID_Parent (creates the ID if the current row is a parent)
=IF([Parent_Serial]@row = 0, "EHR" + [Parents_Count]@row, "")
• ID_Child (creates the ID if the current row is a child)
=IF([Parent_Serial]@row <> 0, INDEX([ID_Parent]:[ID_Parent], MATCH(PARENT(AutoNum@row), AutoNum:AutoNum, 0)) + "." + [Child_Count]@row, "")
• Parent_ID (returns the ID of the parent row for the current child row)
=INDEX([ID_Parent]:[ID_Parent], MATCH(PARENT(AutoNum@row), AutoNum:AutoNum, 0))
III. Create your ID column. (I'm assuming you want this to be the sheet's primary column)
• ID
=IF([ID_Parent]@row <> "", [ID_Parent]@row, [ID_Child]@row)
This approach assumes that:
- All new rows are parent rows. When a row is moved under a parent, the helper columns will change accordingly and the row's ID will also change. (see EHR1.1)
- All child rows added to or moved into a parent row will be assigned the sequential child ID based on the order of its serial number. (see EHR4.2)
Answers
-
I have seen this done before but using a combination of the following formulas.
- what level =COUNT(ANCESTORS([Column]@row))
- is it a parent? =IF(COUNT(CHILDREN()) > 0, 1, 0)
- Rankavg
- =IFERROR(RANKEQ([Row #]@row, [Row #]:[Row #], 1), "")
- some kind of Matching and Vlook-ups with indexing. (I didn't make it but I saw it)
I'll dig and see where it was done, it's been a long time! If I find anything I will let you know.
Joe Goetschel | Smartsheet Director | SCS CLOUD - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
@Joe Goetschel Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!