Work Breakdown Structure (WBS) - Column Formula Configuration

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 , @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.

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