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 auto-numbering)
- 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 auto-numbering)
- 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 1-4)
-
Ok. Let me build a mock-up and do some testing.
-
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! :)
-
Happy to help. 👍️
-
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.
