Automated numbering of parent items and tasks

Elisabeth
Elisabeth ✭✭
edited 12/09/19 in Smartsheet Basics

Hello Community,

I have imported a project list of about 250 tasks from excel into smartsheet, divided into different parent topics. I've numbered these into 3 levels (like: 01.1.1.) manually within the primary column.

If I now add a task in the middle of the list, I theoretically need to change all numbers afterwards... I'm sure there is a smarter way to number all the task automatically?

Can you help me, how to do it?

THANK you very much.

Elisabeth 

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Smartsheet does not currently have a built in WBS system, but HERE is a published link to a sheet I built that takes care of that with up to 6 hierarchy levels.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Paul,

    Nicely done!

    Thanks for sharing!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Thanks! I had a few other solutions in the past that were much less flexible and much more bulky. I was working on a different project and the idea hit me that I had been over thinking it the entire time. All I needed to do was run out the hierarchy list and then count how many times that row's data was repeated.

  • Thanks for the answer!

    To be honest, this looks too complicated for me... Or could you explain to me how to use this method?

     

    Thanks!! :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    SURE!! I built this sheet with ease of replication in mind. All you will need to do to your existing sheet is add a few rows, add a few columns, copy/paste, and find/replace.

     

    So here would be the absolute easiest way for you to set this up.

    .

    Change the column name for whatever column you are basing the WBS off of to "Task". I assume you will be basing it off of your Primary Column simply because that is the column that has the visual indicators of the hierarchy.

    .

    Next you need to figure out whether or not you want the "spacer" columns (the gray bars in between sections). They are completely unnecessary for the solution to work. I only used them for display purposes to make the sheet a little easier to read and figure out.

    .

    I will provide instruction under the assumption that you WILL be using the spacer columns. If you don't want them when it's all built, you can delete them without affecting any of the automations.

    .

    Add 19 columns. You can leave the spacer column names alone. Rename the columns that are used to match exactly what I have for my column names.

    .

    Add 3 rows to the top of your sheet.

    .

    Copy/paste rows 1 and 2 from my published sheet to your sheet.

    .

    Use the Find/Replace feature.

     

    In the columns of [Stage 1 Check] through [Stage 6 Check], replace

     

    $2

    with

    $1

    .

    In the columns One through Six, replace

     

    $3

    with

    $2

    .

    You will then go one column at a time using the following steps:

    1. Copy the cell in row 1.

    2. Click on the cell in row 4.

    3. Type an equals sign. =

    4. Paste the formula that you have already copied from row 1 into the cell in row 4 immediately following the =.

    5. Go to the next cell.

    .

    Do this for every column that has a formula in it.

    .

    Dragfill all of those formulas down to the very last row.

    .

    You can stop here if you want as the WBS is technically complete. If you wanted to clean up the sheet a little bit, here are a few adjustments that you can make that will not affect the solution.

     

    You can delete row 1.

    You can delete row 3.

    You can delete the spacer columns.

    You can rename whatever columns you want (the formulas will automatically update themselves with the correct column names).

    You can hide all of the extra columns that don't need to be displayed.

    If you don't have and don't plan to have that many levels of hierarchy, you can delete the corresponding columns from your sheet. For example... If you will never have more than 3 levels, you can actually delete these columns...

     

    [Stage 4 Check]

    Four

    [Stage 5 Check]

    Five

    [Stage 6 Check]

    Six

    .

    I hope this helps. Feel free to ask any more questions or voice any concerns. yes

  • THANK YOU so much for the detailed description! 

    I will try my luck with it. :)

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • @Paul Newcome can you please share this again? I get a message that the sheet is no longer published. thank you!


    Smartsheet does not currently have a built in WBS system, but HERE is a published link to a sheet I built that takes care of that with up to 6 hierarchy levels.