WBS Numbering Error
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:
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
Answers
-
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.
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
-
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.
-
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:
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:
I hope this helps, let me know if you need help connecting the template to your existing setup!
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
-
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
-
What formula do you have in the [Child Index] column?
-
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))
-
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.
-
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)
-
How exactly are new rows being added to the sheet?
-
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.
-
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?
-
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.
-
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.
-
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
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!