Multilevel numbering.

Options

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?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Heather Duff ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Heather Duff ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Lea Gikas Employee
    Options

    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
    Options

    Thank you very much! You helped me a lot.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!