WBS Numbering Error

Lamont_norberto · 10/01/24

I'm not sure if anyone will be able to help with this one, but I am running into an occasional problem that I don't quite understand.

Essentially, I have a WBS numbering system running to give tasks an ID, and then using the ID to look up a letter equivalent from another sheet, and using that for the Item Label of each task.

The problem is that sometimes it returns a #NO MATCH or sometimes even a 0, which isn't even in the list to choose from. It will also sometimes number rows out of order.

I followed this tutorial to create the WBS system:

https://community.smartsheet.com/discussion/65366/automatic-numbering-for-work-breakdown-structure

The sheet with the letter equivalent:

A screenshot example of the error (the coloured rows are different departments):

Sometimes if I copy/paste the info into new rows the error fixes itself, but not always.

Any insight into how to fix this, or avoid it in the future is apprecaited.

Thanks

kowal · 10/02/24

Hello Norberto,

I look at your dictionary sheet (Column3 & Column4) and I see there is no there is no 1.0 in column3 that's why it gives you "no match" - have you considered starting from 1.0 to be A 1.1 to be B etc. then you will not have this issue at all.

Hope this helps.

Lamont_norberto · 10/02/24

Hi Kowal

Thank you for the replay. Unfortunately that won't work as the WBS system is set to start at 1.1, it shouldn't even generate a 0 to start with.

The Child Index column should be numbering each child row starting at 1, but sometimes it comes back as 0 for some reason.

SSFeatures · 10/03/24

Hi @Lamont_norberto,

The SmartSheet engineers created an official WBS template and it's the "gold standard" at this point for creating a work breakdown structure with automated numbering. I recommend copying the template, delete any columns that you don't need, and migrate your current sheet into your new copy of the template, then modify it to suit your needs.

You can find the template here:

https://www.smartsheet.com/template-gallery/project-work-breakdown-schedule-wbs

I also wrote up a very short guide about the template and how to use it, and I included screenshots. You can find it here:

https://ssfeatures.com/blog/automate-wbs

I hope this helps, let me know if you need help connecting the template to your existing setup!

Lamont_norberto · 10/30/24

Hi @SSFeatures

Thank you for the suggestion, but I looked into this when I was starting the project and it didn't work for what I was looking for. The template has many features I'm not looking to use, and even when I copy/paste the parts I do need, it won't work like the original template.

Thanks

Paul Newcome · 10/31/24

What formula do you have in the [Child Index] column?

Lamont_norberto · 10/31/24

https://community.smartsheet.com/discussion/comment/460390#Comment_460390

Hi Paul,

Here is the formula:

=COUNTIFS(Level:Level, Level@row, [Row number]:[Row number], AND(@cell <= [Row number]@row, @cell > [Parent row number]@row))

Paul Newcome · 10/31/24

And how are [Row ID] and [Row number] being populated? [Row ID] I believe should be an Auto-Number type column which shouldn't have any duplicates in it, but I do see some rows that have duplicates.

Lamont_norberto · 11/04/24

https://community.smartsheet.com/discussion/comment/460461#Comment_460461

Hi Paul

[Row ID] is indeed set up as an Auto-Number column. Agreed about the duplicates, but it is set up as the system column, I haven't been changing anything to it as the sheet gets added to.

[Row number] formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

Paul Newcome · 11/04/24

How exactly are new rows being added to the sheet?

Lamont_norberto · 11/04/24

https://community.smartsheet.com/discussion/comment/460776#Comment_460776

Each sheet is set up with different header rows for each department (the green/red rows in the screenshot of the original post in this thread). As the job progresses, rows are added to each department as needed for tasks/items required. These new rows are indented as children of the header rows so the head of each department is notified, etc.

Paul Newcome · 11/04/24

Ok. But how exactly are they added? Are they copied or moved from another sheet? Copying / moving from another sheet is the only way to get duplicates in an auto-number column. If that is the case, is it absolutely necessary to continue copying / moving from another sheet?

Lamont_norberto · 11/04/24

https://community.smartsheet.com/discussion/comment/460809#Comment_460809

No copying or moving, they're all added via the icon in the menu at the top or right clicking and doing the Insert Above/Below.

Paul Newcome · 11/04/24

In that case, the only way to get rid of duplicates in the auto-number column is to delete it, save the sheet, re-insert it, save the sheet, then fix any formulas that were referencing this column.

Lamont_norberto · 11/19/24

https://community.smartsheet.com/discussion/comment/460838#Comment_460838

This does seem to fix the error. Is there any trick to not getting duplicates in the auto-number column in the first place?

Thanks for the help

Paul Newcome · 11/20/24

@Lamont_norberto You would have to make sure any rows moved or copied from another sheet do not have the same data in the auto-number column in their source sheets.

WBS Numbering Error

Answers

Help Article Resources

Categories