Work Breakdown Structure (WBS) - Column Formula Configuration

Tom ZylstraTom Zylstra Employee
edited 07/07/21 in Best Practice
03/30/21 Edited 07/07/21

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.

Here is a distribution link to create a copy in your account; start creating tasks and sub-tasks and the configuration will activate - https://app.smartsheet.com/b/launch?lx=AoXtVB0iMz0XS5D-MVU5jl2F3tjZfBYMXSEruozjq1E

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

Regards, Tom Zylstra, Lea Gikas, Dave Stanley

Comments

  • Mike ArntzMike Arntz Employee

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

  • Bassam KhalilBassam 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.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • @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 SmithJonathon 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 + "." + [email protected], "")


    And if you change you level formula to

    =IF(COUNT(CHILDREN([email protected])) > 0, COUNT(ANCESTORS([email protected])) + 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 KarkhanisSameer 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 CSing 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 SalgueroLuis 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([email protected] = "", [email protected] > TODAY()), "Blue", IF(OR([% Complete]@row = 1, [email protected] > TODAY(+[Task Duration Health Workdays]#)), "Green", IF([email protected] < TODAY(), "Red", IF(NETWORKDAYS(TODAY(), [email protected]) < [Task Duration Health Workdays]#, "Yellow", "Green"))))), "")


    Thank you

  • Lea GikasLea Gikas Employee

    @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!

  • 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 GikasLea Gikas Employee

    @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

Sign In or Register to comment.