Multilevel numbering.

Olga

Hello how to create automatic line numbering with nested levels. The levels must be dependent on the parent-child nesting line level.

An example is a manually generated numbering. Can it be done automatically?

Paul Newcome

There are a few solutions already out here in the Community. If you do a search for "WBS", you should be able to find a few different ones.

Heather Duff

Hi Olga,

Here's a (cumbersome, but effective) way of doing it:

This setup allows for 4 levels deep (up to A.B.C.D – i.e. 1.2.1.4). For sheets that have more than 4 levels, additional columns would need to be added and the formula in the WBS column adjusted.

Add 6 columns (which can be hidden later):

LEVEL column (row 2): =COUNT(ANCESTORS()) + 1

A column (row 2): =IF(Level@row = 1, A1 + 1, PARENT())

B column (row 2): =IF(Level@row = 2, B1 + 1, PARENT())

C column (row 2): =IF(Level@row = 3, C1 + 1, PARENT())

D column (row 2): =IF(Level@row = 4, D1 + 1, PARENT())

WBS column (row 2): =IF(ISBLANK(B@row), A@row, IF(ISBLANK(C@row), A@row + "." + B@row, IF(ISBLANK(D@row), A@row + "." + B@row + "." + C@row, A@row + "." + B@row + "." + C@row + "." + D@row)))

 Note: The cells in the row 1 in columns A, B, C, and D must be blank; additionally, these formulas cannot be converted to column formulas.

Once you've entered the formulas in the row 2 fields, you can copy them down to the bottom of your sheet.

Hope this helps!

Best,

Heather

Olga

Heather D, many thanks! These formulas work. But could you still help: there is a problem of copying formulas to a new row from hidden columns. If I create a new row in the middle of the sheet, then I need to copy the 6 column formulas. If I hide the auxiliary columns, then the formula cannot be extended. Maybe there is a way to quickly copy formulas to new lines?

Heather Duff

Olga,

One option may be to copy an entire row (one that contains the formulas in the hidden columns), then replace the data for the original line with the new information you are hoping to add.

Best,

Heather

Paul Newcome

Are you setting them as actual column formulas? If so, they should be automatically populating.

Heather Duff

Paul,

Unfortunately, it gave an error message when I tried to convert to column formulas. I think it may have something to do with the fact that it references the previous row, and row 1 wouldn't have a previous row. (For example, on row 3 it references multiple cells on row 2.)

Thoughts on this?

Thanks,

Heather

Olga

That's exactly how it is. Formulas refer to specific cells (not @row). Perhaps this is why they cannot be converted to a column formula. At the same time, copying also causes difficulties.

Are there representatives of Smartsheet developers in this community? It would be nice to finally make this feature standard so that users don't have to look for complicated solutions to this problem.

Paul Newcome

My apologies. I missed that part. There may be a way to get it set up with all "@row" references instead of row specific references. I'll play around with it a little bit and get back to you.

Lea Gikas

Hi Olga, @Tom Zylstra on the Smartsheet team recently posted about a WBS configuration. If you check out his Community Post there is a link that can give you a direct link to a sheet that has the WBS structure built in that you can start using: https://community.smartsheet.com/discussion/77827/work-breakdown-structure-wbs-column-formula-configuration

Cheers,

Lea

Olga

Thank you very much! You helped me a lot.