Work Breakdown Structure (WBS) - Column Formula Configuration

Tom Zylstra

Hello Everyone,

Recently myself and two fellow Smartsheet Sales Engineers, @Lea Gikas and @Dave Stanley got together to create a WBS solution powered by Column Formulas.

We wanted to provide the resulting configuration to the community in case some may find it helpful in executing project management solutions, this configuration supports up to ~2,300 tasks & metrics are built out through WBS Level 6.

The solution uses 7 hidden and locked column formulas, and optional Summary Sheet formulas that provide WBS related metrics.

Update: Here is the link to the template in the Solution Center - https://www.smartsheet.com/marketplace/templates/work-breakdown-structure-wbs

We hope this is helpful and let us know if you have any questions.

Regards, Tom Zylstra, Lea Gikas, Dave Stanley

Mike Arntz

Tom, Lea and Dave - Thanks for creating and posting this!

Bassam Khalil

Hi @Tom Zylstra

What you have done is more than amazing and will benefit a large number of SmartSheet users. Thank you so much.

Tom Zylstra

@Bassam Khalil You are welcome and thank you for the kind words, this solution was quite a challenge to solve for using only Column Formulas in order to deliver the highest possible performance WBS solution that could also be easily deployed by any customer around the world with a simple "Save As". Cheers, Tom

Jonathon Smith

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

Thank you for coming up with this simplified WBS format. I've been working on this myself off and on again to come up with a way that would only use column based formulas.

The way that you currently have all of your summary metrics setup will give feedback on tasks based each indentation level. If you were to change each formula to reference the "phase" or top level number

=IFERROR((INDEX(Task:Task, MATCH("2", WBS:WBS, 0))) + " - " + ROUND((INDEX([%Complete]:[%Complete], MATCH("2", WBS:WBS, 0))) * 100) + "%", ("-"))

You could then get outputs like this.

Also you can simplify the output for "UniqueID"

=IFERROR([Parent ID]@row + "." + AutoNum@row, "")

And if you change you level formula to

=IF(COUNT(CHILDREN(Task@row)) > 0, COUNT(ANCESTORS(Task@row)) + 1)

This would make it so that your bottom level task will have a level of " ", not matter what level of indentation your task is at.

Sameer Karkhanis

@Tom Zylstra Thanks for sharing this. While the WBS column is great workaround I would prefer Smartsheet team incorporate this inbuilt for project sheets. Hiding and unhiding column is not very sophisticated implementation in Smartsheet so that adds to another issue.

I have been looking for creating a distribution link (for solution sets) like you did, can you help with on how to go about it?

Sing C

@Tom Zylstra, @Lea Gikas, @Dave Stanley This is incredible. Thank you! We created a standard project plan template a while ago and will be integrating some of the great work from what you've put together into that.

Luis Salguero

@Tom Zylstra Thanks for sharing the WBS sheet.

Could you please explain how to use the Health formula? I would like to use it in other projects.

=IFERROR((IF(OR(Start@row = "", Start@row > TODAY()), "Blue", IF(OR([% Complete]@row = 1, Finish@row > TODAY(+[Task Duration Health Workdays]#)), "Green", IF(Finish@row < TODAY(), "Red", IF(NETWORKDAYS(TODAY(), Finish@row) < [Task Duration Health Workdays]#, "Yellow", "Green"))))), "")

Thank you

Lea Gikas

@Luis Salguero Hi Luis! Great question. This formula takes into consideration the "Finish" (date) column and the "Task Duration Health Workdays" Sheet Summary field when assigning a color to the Health field. If you'd like to continue using both of these fields, the best way to interact with this formula is to enter the appropriate Start and Finish dates and then assign a number to the "Task Duration Health Workdays" field.

The "Task Duration Health Workdays" field is a field you can edit. Currently, the number 4 is there. This means that when the Finish date is within 4 days and the task is still not complete, we turn the health Yellow. This allows us to give a heads-up that a task is at risk of being late, instead of turning it from Green to Red with no warning. Some folks might like a 7 day notice, some prefer 1 day. I hope this answers your question!

JPJPJP

Question - The post says "supports up to ~2,300 tasks"... what happens after that count?

This numbering format is exactly what I was looking for, I'd like to use it for our project tracker which includes the deliverables as sub-items. There's thousands of projects in the tracker sheet and many deliverables under each one, and they continue to grow every day. So if it's limited to ~2,300 tasks this solution won't work for me. I really wish this was baked into Smartsheet in the AutoNumber type column. Either way, if this is indeed a limit, do you have any other suggestion?

Lea Gikas

@JPJPJP Hello, another great question. Formulas in sheets impact the performance (load time, speed of scrolling etc.) of the sheet because a calculation is being performed. In many sheets, this performance change actually wouldn't be noticeable. The formulas, combined with the number of rows, however does impact performance slightly more. the ~2,300 rows that @Tom Zylstra mentioned was a recommendation for the solution, meaning that if you add more than 2,300 rows to the sheet, you might see degraded performance in load speed.

My Recommendation: does each project plan have more than 2,300 rows? if so, I would recommend using multiple WBS templates. You could organize them by WBS level (for example, all of Level 1 and Level 2 on 1 sheet, then on the second sheet WBS Level 3 and 4). From there, you can actually use a Smartsheet report to roll them up as 1. While sheets have row limitations, a Report can roll up over 1 million rows! Please let me know if this helps and if you have any other questions.

Thanks,

Lea

Brian Cunningham

What is the purpose of the UniqueID Column? Can't you just use the AutoNum column in the formula for Suffix? I'd think the AutoNum will always be unique.

Tom Zylstra

Hi Brian, thanks for the question regarding the need for the "UniqueID" formula column in the WBS solution. It is designed to provide additional functionality to ensure that in cases of projects with multiple tasks using the same value (i.e. "Adapt Region to UTC Time") these tasks can always be uniquely identified from each other via the UniqueID value because it combines the "Task" value and "AutoNum" values. You are spot on that the AutoNum value is always unique, but it is in our experience not as easy to recall/identify/search in large scale operation.

Also the UniqueID is leveraged if the WBS solution needs to be enhanced to additionally support a Cost Breakdown Structure (CBS) capability as the UniqueID column is used as a dropdown value in a CBS cost submission Smartsheet form created in the WBS Sheet. Hope this helps, Tom

HardWork

How is the Duration column formula set up? Can you share the formula? I can't seem to get it to show in the template that you shared.

Tom Zylstra

Thank you for the question regarding Duration in the WBS configuration. In Smartsheet the Duration column is a special "Project Settings" column that is not modifiable, it is automatically calculated whenever the Start Date and/or End Date of a Task changes. Hope this helps, Tom

RichC

Hi @Tom Zylstra,

I've downloaded the template and the numbering of subtasks doesn't seem to reset after each new task. Did it somehow get corrupted or am I thinking strangely? Here's what I see. Thanks in advance for your assistance. If I can get this to work it will be a godsend.