Creating WBS formula for deliverables spread out over phases

Aly_Olson_Turek

I have a project schedule that is organized by phases. Tasks for each deliverable are spread throughout the phases. The team has requested we add a WBS ID to the project schedule so we can identify/track the work across the phases. I'm trying to create a formula to generate the WBS.

My formula to create the WBS Phase - 0 is working to give a distinct ID to each "Parent Deliverable," however my formula is not producing sequential values, e.g. 1, 2, 3, but instead seems to be counting distinct values that do not meet the criteria in my formula.

My formula for WBS Phase - 0 is:

=IF(Place@row = 2, MATCH([Task Name]@row, DISTINCT([Task Name]:[Task Name]), 0))

In my screenshot, you'll see that WBS Phase - 0 value is 60, when I would want it to be "2," because it's the second unique Parent Deliverable in the project schedule. This same problem is then happening in my WBS Phase - 1, and WBS Phase - 2.

Paul Newcome

There are a number of WBS threads here in the Community that should have what you are looking for. I think the most recent would be the one referencing column formulas.

https://search.smartsheet.com/community/#q=wbs&t=CommunityPosts&sort=relevancy&numberOfResults=25

.

Aly_Olson_Turek

@Paul Newcome I started with the Smartsheet template for the WBS, however that formula produces a number assuming the WBS items are all grouped together in a single parent/child grouping, whereas mine are split out over numerous ones across multiple phases. I've tried to modify the template formulas to produce what I want but have run into the above issue.

Paul Newcome

Are you able to provide some manually entered screenshots to show what you are trying to accomplish?

Aly_Olson_Turek

Here's a screenshot of what I'm trying to create - see the column for Desired WBS. @Paul Newcome

• Every row of "Account Management / Account Services" would have same WBS ID across phases
• Deliverables underneath that would have unique IDs, but consistent IDs across phases (e.g. see Readiness Review Delegation Audit (if applicable)
• Child tasks underneath the Deliverable would have unique IDs that would continue across phases - see yellow bracketed rows

Paul Newcome

You will need to use a solution that gets the row number output on every row so that it can be leveraged in formulas. I generally use an auto-number column called "Auto" and a text/number column called "Row" with this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then you would need to work a COUNT/DISTINCT/COLLECT into your current setup to count how many distinct values there are in the [Task Name] column that share the same parent text and have a row number lower than the current row number. I would suggest using a column that outputs all ANCESTORS in a string and evaluating that in the COUNT/DISTINCT/COLLECT combo to simplify things.

=JOIN(ANCESTORS(), "-")

Then the COUNT/DISTINCT/COLLECT would look something along the lines of

COUNT(DISTINCT(COLLECT(Ancestors:Ancestors, Row:Row, @cell <= Row@row)))

This would end up being leveraged somewhere in your existing WBS structure, to output the final number you need for each particular row. From there you would use whatever method you are already using to join together each of the prefixes for the W.B.S..