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
-
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!
-
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?
-
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
-
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!
-
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.
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!