Automated numbering of parent items and tasks

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.

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.

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. 

Happy to help! 

 

Feel free to ask any questions you may come across!