Work Breakdown Structure (WBS) - Column Formula Configuration

24

Comments

  • Neil Watson
    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
    Tom Zylstra Employee
    edited 02/24/22

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

  • @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
    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
    Mark Ryan Overachievers

    This is superb!! Many thanks for sharing

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

  • TonyB
    TonyB
    edited 08/16/22

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

  • 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

  • Works like a charm, thanks! Manages additions and deletions

  • steve brown
    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
    Lea Gikas Employee

    @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
    steve brown ✭✭✭

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

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