Automated numbering of parent items and tasks

Elisabeth

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 

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å

Paul,

Nicely done!

Thanks for sharing!

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.

Elisabeth

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

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. 

Elisabeth

THANK YOU so much for the detailed description! 

I will try my luck with it.

 

Paul Newcome

Happy to help! 

 

Feel free to ask any questions you may come across!

nicole kindschi

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