How can I get my subtask to be counted in the same format as my work breakdown structure?

Kaseem Gomez

The subtask in my work breakdown structure is being counted as 1.1 (first screenshot) and I want this to be the same for subtask on my intake form (second screenshot). Is there a way I can get my subtask counted in the same format? I currently don't want the subtask to be counted as 6.107, I want it to be counted as 1 and so on.

Philip Robbins

This is what it gives me:

Philip Robbins

@Kaseem Gomez would you mind sharing a little more information to help us understand the challenge in more detail? Do you have an auto-number field set up in second screenshot that's assigning 106, 107 along with some kind of prefix?

Also, how do the rows in the first screenshot correlate with those in the second?

Kaseem Gomez

@Philip Robbins Thank you for your help! These two screenshots do not correlate but I put that first screenshot as an example of how I want the Subtask to be counted on the second screenshot. In the second screenshot, there is a text/number column with a formula to count projects by department. In the second screenshot, the project is counted as OCM-2024-106 (OCM is the department, 2024 is the year, and this is project 106 for OCM). The subtask to the example project is counted as 6.107. I would like the subtask to be counted similar to how subtask are being in the first screenshot. In short, how can the subtask in the second screenshot be counted as 106.1 or simply 1 and so on.

Philip Robbins

@Kaseem Gomez thanks for clarifying. I'd probably need to see your sheet with all the required columns and headers to give a full solution, but this can definitely be done.

If the numbering of the subtasks that you want to append to the Department-Year-Project string is in the row already, and you're entering those numbers manually, then you would just concatenate them using =[Column1]@row+[Column2]@row. If you want to generate the subtask numbering automatically there are some helper columns required.

If you can share the column headers I'll try to suggest the exact formulas and which columns to put them in.

Kaseem Gomez

@Philip Robbins here is the formula I used along with the column headers.

=IF([Approval Status]@row = "Approved", LEFT(Department@row, 3) + "-" + (YEAR([Project Intake Date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + [Suffix Helper]@row + ""))

Philip Robbins

Okay, I'm going to focus on getting you a column that gives you your WBS number (up to 2 levels) and leave you to work it into your logic above.

First column:

• Name: Ref
• Type: AutoNum (all settings default - just needs to create integers)

Second column:

• Name: Row
• Type: Text/Number
• Formula: =MATCH(Ref@row,Ref:Ref,0)
• This creates an absolute sequence of row numbers regardless of whether you reorder rows.

Third column:

• Name: Level
• Type: Text/Number
• Formula: =COUNT(ANCESTORS())
• This will give you the level of the WBS. Note the top level (Summer Transfer Campaign) will be zero and won't have a WBS number.

Fourth column:

• Name: WBS Level 1
• Type: Text/Number
• Formula: =IF(Level@row=1,COUNTIFS(Level:Level,Level@row,Row:Row,<=Row@row),IF(Level@row=2,PARENT(),""))

Fifth column:

• Name: WBS Level 2
• Type: Text/Number
• Formula: =IF(Level@row = 2, COUNTIFS(Level:Level, Level@row, [WBS Level 1]:[WBS Level 1], [WBS Level 1]@row, Row:Row, <=Row@row), "")

Sixth column:

• Name: WBS
• Type: Text/Number
• Formula: =IF(Level@row=1,[WBS Level 1]@row,IF(Level@row=2,[WBS Level 1]@row+"."+[WBS Level 2]@row,""))

Philip Robbins

This is what it gives me:

Kaseem Gomez

@Philip Robbins thank you! This was very helpful. Problem solved.