Work Breakdown Structure (WBS) - Column Formula Configuration

Neil Watson

@RichC mine numbers properly (using the downloaded template). Worth checking the hidden columns to see whether they have become mixed up.

Tom Zylstra

Hello @RichC @Rich C ,

That is interesting that you experienced the WBS logic not properly "resetting" the sub-task numbering to 1 in an instance. I was not able to recreate your issue and as a quick next step recommend that you use the provided distribution link to create a new instance of the WBS Sheet and see if the issue is resolved.

NOTE: We are using the template with some customers that have a large number of projects and to date the configuration is operating as designed, we want to make sure to work with you to get you to the same desired operational state. Regards, Tom

tsuranyi

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

Thank you for the template. It is great! I'm wondering if you can assist as we usually have the project title in the first row and then use the percentage complete, etc. off of that first line for our dashboard. Our stakeholders also really like seeing that first row with the project title and summary info. Of course, because of this, everything then is indented from the first project row, so it is messing with the "Ancestor=0" part of your formulas. Do you have a way to leave out the first row in your calculations and then modify the other main headers to basically ignore this first row so that they show up as 1, 2, 3, etc. in the WBS calculations?

tdbozarth

@Tom Zylstra Thank you. In your template I see the Health column which is very similar to something I have been trying to figure out. I am currently an MS Project user evaluating Smartsheet. I have developed a status/health field in MS project that does the following and am trying to sort out if Smartsheet can do this.

If Start Date is more than 2wks out, color white

if Start date is within 2wks, color grey

If start date has passed and % complete is on track, green

if start date has passed and % complete is behind, orange

if finish date has passed and not 100% complete, red

If complete, blue

Can this be done in smartsheet? A key element is smartsheet would need to have a way to calculate what % complete should be for the task to be considered on-track.

thanks!

tsuranyi

Hi, just checking in re my question re leaving out the first row in the WBS calculations. I've provided a screenshot of what I was referencing using one of the Smartsheet templates.

https://app.smartsheet.com/sheets/473rcVj8Q4phg24VRw2RQ97jWJrvGxvm3VCr9MX1?view=gantt

Mark Ryan

This is superb!! Many thanks for sharing

Smtshtuser

Could you please share the sheet with me as well. I have already requested access.

TonyB

Hello I am trying to add the WBS columns to an existing sheet. Does anyone have the formulas and columns in a copy-able format? The linked sheet won't allow me to copy the formulas for some reason.

This linked sheet is what I am referring to....

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

Neil Watson

@TonyB when I click on the link, a folder is created in my sheets section called "WBS Configuration" with a file named "Template - WBS Configuration" therein.

Double-clicking on a cell allows me to edit the formula and copy it (exception of the auto numbering field "AutoNum"

Is that not the same for you?

Tom Zylstra

Hello Everyone,

We have an update on the topic of WBS solutions, the solution we posted about last year in this discussion has just been promoted to the Smartsheet Solution Center as a formal solution.

On behalf of my colleagues @Lea Gikas and @Dave Stanley thank you for the tremendous interest and in depth feedback you have provided.

For all of you who have implementation questions, or going forward would like to use this WBS solution; please navigate to the Smartsheet Solution Center and use the WBS Template.

Cheers,

Tom Zylstra

Guy Warwick

Works like a charm, thanks! Manages additions and deletions

steve brown

This (would be) so great but I can't make it work by just dropping this formula...

=IF(prefix@row<>",prefix@row,"")+suffix@rowan.bradley

WHAT AM I DOING WRONG?

Also your templates formula is not able to be accessed (like the highlighted blue in the jpg below is available) even after unlocking the column.

THANKS!

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

into a cell. I get the message "UNPARSEABLE"

Lea Gikas

@steve brown Hi Steve, thanks for the comment! I believe I found the missing part of your formula. After the first "prefix@row<>" you have 1 quotation. You'll need 2 of them. The formula should look like this: =IF(Prefix@row <> "", Prefix@row, "") + Suffix@row

Please let me know if this works for you.

steve brown

Thanks for taking the time Lea. Cut and pasted it in, but it didn't work. 3

https://drive.google.com/file/d/1fAyqaMN9o7Ua2LUziUvS9V7EJ8AwHxd7/view?usp=drivesdk

If I shared the sheet with you, would you be willing to take a stab at it? I am sure I am missing something obvious, I don't really know the "IF" statement language. I just used an example in this forum.

Steve

Erik B

Hello Everyone,

I have a question about using this template. My sheets start on row 28 because we are using control center to copy information into the first 27 rows, they are just hidden. Is there a way to modify this formula so that the numbering begins on a specific row? Row 28 if I am being specific? Thanks for your help!