WBS in smartsheet.
Hi. Is there a way that I can have a dewey decimal system kind of the numbering for the items on my worksheet. I have found a solution that lets me have items numbered in the format 1.1 and 1.1.2 and 1.1.2.4 etc. But I want a system where I can have the format 01.00.00.00 and 12.04.09.11 etc; How do I make sure that there are two digits shown (even if one digit is zero  like 01) in each component and also always have four components shown (that are separated by 3 dots)? Please let me know if someone know how to get this format! Thanks in advance :)
Best Answer

Alright. So I created two more columns. [Base WBS] and [Helper].
The formula for the [Base WBS] column is:
=IF(Level@row = 1, IF(COUNTIF(Level$1:Level@row, 1) < 10, "0") + COUNTIF(Level$1:Level@row, 1), "" + PARENT() + "." + IF(COUNTIFS(Level$1:Level@row, Level@row, RowID$1:RowID@row, > PARENT(RowID@row)) < 10, "0") + COUNTIFS(Level$1:Level@row, Level@row, RowID$1:RowID@row, > PARENT(RowID@row)))
The formula for the Helper column is:
=IF(LEN([Base WBS]@row) <= 8, ".00") + "" + IF(LEN([Base WBS]@row) <= 6, ".00") + IF(LEN([Base WBS]@row) <= 4, ".00")
And finally the formula for the WBS column:
=[Base WBS]@row + Helper@row
Answers

What is the solution you are currently using to generate a
1.1
1.1.2
1.1.2.4
etc
?

Hey Paul,
I found this solution on a smart sheets forum and currently using it for my project:
1. Create 3 Columns (all in text/number format, don't use autonumbering)
 RowID
 Level
 WBS
2. Put the number 1 in the top row for all 3 (or for the WBS, whatever number you're starting with)
3. In the second row put the following formulas:
 RowID: =RowID1+1
 Level: =COUNT(ANCESTORS()) + 1
 WBS: =IF(Level2 = 1, COUNTIF(Level$1:Level2, 1), "" + PARENT() + "." + COUNTIFS(Level$1:Level2, Level2, [Row ID]$1:[Row ID]2, >(PARENT([Row ID]2))))
Hope this helps. Thanks!

Hey Paul,
Currently I am using this solution which I got from a smartsheets forum:
1. Create 3 Columns (all in text/number format, don't use autonumbering)
 RowID
 Level
 WBS
2. Put the number 1 in the top row for all 3 (or for the WBS, whatever number you're starting with)
3. In the second row put the following formulas:
 RowID: =RowID1+1
 Level: =COUNT(ANCESTORS()) + 1
 WBS: =IF(Level2 = 1, COUNTIF(Level$1:Level2, 1), "" + PARENT() + "." + COUNTIFS(Level$1:Level2, Level2, [Row ID]$1:[Row ID]2, >(PARENT([Row ID]2))))
Hope this helps! Thanks!

Are you able to provide a screenshot of this in action? I am sure there is a way to accomplish exactly what you want but being able to see what we are working with and put it in context would really help.

Hey Paul,
Sure. Here you go. The first 3 columns are created as a part of the solution that I mentioned in the previous post. But I want a 01.00.00.00 and 12.02.09.14 kinda format. Please let me know if there is a way to do so. Thanks!
(please ignore rows 14)


Alright. So I created two more columns. [Base WBS] and [Helper].
The formula for the [Base WBS] column is:
=IF(Level@row = 1, IF(COUNTIF(Level$1:Level@row, 1) < 10, "0") + COUNTIF(Level$1:Level@row, 1), "" + PARENT() + "." + IF(COUNTIFS(Level$1:Level@row, Level@row, RowID$1:RowID@row, > PARENT(RowID@row)) < 10, "0") + COUNTIFS(Level$1:Level@row, Level@row, RowID$1:RowID@row, > PARENT(RowID@row)))
The formula for the Helper column is:
=IF(LEN([Base WBS]@row) <= 8, ".00") + "" + IF(LEN([Base WBS]@row) <= 6, ".00") + IF(LEN([Base WBS]@row) <= 4, ".00")
And finally the formula for the WBS column:
=[Base WBS]@row + Helper@row

Hey Paul, You are awesome! Thanks a lot! I really appreciate it. It works! :)


Naveen,
I am receiving the #unparseable error in the WBS column using your formula. I'm not sure what may be wrong. Below is a screen shot.
Any thoughts?

I am also receiving the #unparseable error in the WBS column using the formula. Where is the orginal post this is shared so I could look it up?

@Al Calabrese You should be able to do a search within the Community on "WBS". If you are still unable to find it, are you able to provide a screenshot that shows the formula in the sheet itself similar to Kim's screenshot?

The issue the two above users are having is the space in the Row ID reference in the formula. @Al Calabrese and @Kim Norwood
 WBS:
=IF(Level2 = 1, COUNTIF(Level$1:Level2, 1), "" + PARENT() + "." + COUNTIFS(Level$1:Level2, Level2, [Row ID]$1:[Row ID]2, >(PARENT([Row ID]2))))
vs
 WBS:
=IF(Level2 = 1, COUNTIF(Level$1:Level2, 1), "" + PARENT() + "." + COUNTIFS(Level$1:Level2, Level2, [RowID]$1:[RowID]2, >(PARENT([RowID]2))))
 WBS:

@James Keuning Good catch. Thank you.
Yes. The column names in the formulas must match the column names being used in the sheet.