WBS in smartsheet.

Naveen Srikakulam · 08/04/20

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 · 08/04/20

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 · 08/04/20

What is the solution you are currently using to generate a

1.1

1.1.2

1.1.2.4

etc

?

Naveen Srikakulam · 08/04/20

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 · 08/04/20

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 · 08/04/20

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 · 08/04/20

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 · 08/04/20

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

Paul Newcome · 08/04/20

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 · 08/04/20

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

Paul Newcome · 08/05/20

Happy to help. 👍️

Kim Norwood · 09/29/20

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 · 11/20/20

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 · 11/23/20

@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 · 12/31/20

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 · 01/04/21

@James Keuning Good catch. Thank you.

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

WBS in smartsheet.

Best Answer

Answers

Categories