WBS with a 3rd level and using letters

Melissa Torrez

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)

Joe Goetschel

I have seen this done before but using a combination of the following formulas.

1. what level =COUNT(ANCESTORS([Column]@row))
2. is it a parent? =IF(COUNT(CHILDREN()) > 0, 1, 0)
3. Rankavg
4. =IFERROR(RANKEQ([Row #]@row, [Row #]:[Row #], 1), "")
5. 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.

Melissa Torrez

@Joe Goetschel Thank you!