Automated numbering of parent items and tasks

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

Find more posts tagged with

Comments

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

with

In the columns One through Six, replace

with

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…