Multilevel numbering.

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 ✭✭✭✭✭✭

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Heather Duff
    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

  • 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 ✭✭✭✭✭✭

    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 ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Heather Duff
    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

  • 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 ✭✭✭✭✭✭

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lea Gikas
    Lea Gikas Employee

    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

  • 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!