WBS in smartsheet.

Naveen Srikakulam

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 :)

Paul Newcome

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

Paul Newcome

What is the solution you are currently using to generate a

1.1

1.1.2

1.1.2.4

etc

?

Naveen Srikakulam

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!

Naveen Srikakulam

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!

Paul Newcome

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.

Naveen Srikakulam

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)

Paul Newcome

Ok. Let me build a mock-up and do some testing.

Paul Newcome

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

Naveen Srikakulam

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

Paul Newcome

Happy to help. 👍️

Kim Norwood

https://community.smartsheet.com/discussion/comment/258676#Comment_258676

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?

Al Calabrese

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?

Paul Newcome

@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?

James Keuning

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

Paul Newcome

@James Keuning Good catch. Thank you.

Yes. The column names in the formulas must match the column names being used in the sheet.