Work Breakdown Structure (WBS) - Column Formula Configuration

Victoria B

New to Smartsheets, and am hoping to create reports for each work package and it's tasks, but am having a hard time filtering in the report. Any suggestions on how to build a report that lists all of the elements and tasks for WBS with prefix of 1 and a separate report with those with a prefix of 2?

Neil Watson

Hi @Victoria B, when you create the report bring in the suffice column (you can always hide it) and use that to filter 1, 2 etc.

Julie Silverman

Samuel Mueller

Thank you so much @Tom Zanzola this led me to get exactly what I have been trying figure out for ages (couple hours lol). Didn't even cross my mind that using just match would return the location. I just wanted a running count of children, like first child, second child so I can add a number in front of the child. Love it!

Melissa Larkin

Hey Tom Zylstra! Not sure if this is a weird request but what would I have to change to the template to make the WBS start with zero so it's 0, 0.1, 0.1.1, 0.1.2, etc.? Not sure if it's possible but thought I ask.

Pat Canning

@Tom Zylstra

Edit: The webpage display eliminates my leading spaces thereby flattening the indented hierarchy.

Great work! I'm a practitioner of using the first row in a project plan / schedule for a whole-project summary row. Rather than your:

Frame

Roof

Mine would be:

Barn Building Project

Frame

Roof

Using your formulas I'd then get the following with all WBS numbers beginning with "1". :-(

1 Barn Building Project

1.1 Frame

1.2 Roof

So I modified your first 3 formulas to the following and then use a keyword in the Task column to make it work.

Prefix:  =IF(OR(RIGHT(Task@row, 9) = "Project>>", RIGHT(Task@row, 9) = "Program>>"), "", PARENT() + IF(ParentID@row = "TOP", "", PARENT(Suffix@row) + ".")) 

Suffix:  =IF(OR(RIGHT(Task@row, 9) = "Project>>", RIGHT(Task@row, 9) = "Program>>"), "", MATCH(UniqueID@row, COLLECT(UniqueID:UniqueID, ParentID:ParentID, ParentID@row), 0)) 

ParentID:  =IF(OR(RIGHT(Task@row, 9) = "Project>>", RIGHT(Task@row, 9) = "Program>>"), "", IF(COUNT(ANCESTORS()) = 0, "TOP", "R" + PARENT(AutoNum@row)))

Now I get the hierarchy I want:

(no WBS) Barn Building Project

1 Frame

2 Roof

Kayla Q

@Rich C I'm having this same issue. Did you ever get this figured out? It's super annoying, and doesn't function like a proper WBS.

Neil Watson

@Kayla Q did you download the template from the Solution Center here: https://www.smartsheet.com/marketplace/templates/work-breakdown-structure-wbs

Kayla Q

@Neil Watson ah, I see!

What a pain. I have 6 charts I need to add these new columns to, and then I need to redo my function :)

Neil Watson

@Kayla Q, copying the rows from the WBS to the existing sheets at least creates the columns but since you have to redo the formula not much time is saved. Doing it from scratch has the benefit that you work out how the system works which can be useful if you want to tweak the WBS later.

Kayla Q

@Neil Watson you're totally right! I understand it a lot better now :)

Unfortunately, I'm using this in conjunction with Control Center, which means I have to have a summary data section at the top of my sheet. It's causing my first Task ID to default to the following:

Any suggestions for how I might make that first "Tasks" bar default to 1 instead of 3?

Neil Watson

@Kayla Q see post earlier from @Pat Canning in this thread which I believe is similar to your requirement - by using IF statements you can ignore the summary data section rows

Tamara

Hello Smartsheet Community,

I am looking for a formula to compare two dates in the WBS template. I have several Projects split up using the Parent/Child structure. I have two rows under Task Name, Shipping and Goal End Date. If the Goal End Date exceeds the shipping date, I need to check a box. Any advice would be greatly appreciated. Thanks!

CraigO

Hi @Tom Zylstra, @Lea Gikas, @Dave Stanley,

Great work on the Work Breakdown Structure, it has saved a crazy amount of time.

We wanted to utilize the WBS to prioritize project tasks in our project dashboards. However, when sorting by the WBS column in reports, we see this odd behavior when sorting:

Have you seen this behavior before?

Sheryl P

I have seen this behavior in many applications when the data type is "text" rather than a "number" - I'm not sure if Smartsheet has a function to convert it to a number that would then sort so that 10 comes after 9 instead of after 1