Auto-Number generated for parent/children rows

Daniel Barber

I am trying to build up a WBS and would like set a number generator to auto ID the various levels (control accounts and work packages).

I would like to have the parent row, or control account, have an ID of 1.1 and the work packages, or child rows indented from there, to have an ID of 1.1.1, 1.1.2, 1.1.3, etc.

Being a little newer to Smartsheets, I am struggling to set this up. So far each row, regardless of if its a parent or child, gets numbered sequentially.

Is there a way to achieve the numbering system I mentioned above?

Paul Newcome

HERE is a link to a published sheet containing a WBS solution. Let me know if you need any help or have any questions.

Daniel Barber

Thanks for your link, Paul. I've tried playing around with it over the last couple of days but cant seem to get it to work on my end. Would you mind helping me break down the formula for my purposes?

This is a basic view of what I would like to achieve, where Column 10 would read the IDs, Rows 2 and 4 would be parent rows (1.0, 2.0) and Rows 3 and 5 would be the start of the children rows (1.1, 2.1, etc.)

Im relatively new to Smartsheets and to this logic and am unsure how to use these formulas. Any help would be appreciated. Thanks!

Paul Newcome

You don't need the grey spacer columns, but you will wan to create every other column from [Ancestor] on to the right. The [Join] column would be your [Column10].

If you name your columns exactly as what is in the example, you can copy/paste the formulas from the example directly into your sheet (after you add the = at the beginning).

Once you have done that, you can rename whatever columns you would like, and the formulas should update with the new column names automatically.

Kristen Barber

Hi Paul, your template is great and exactly what I'm looking for. I copied it exactly (at least I think so), but continue to get #BLOCKED and #UNPARSABLE errors. Should my Stage # Check columns be checkboxes?

Paul Newcome

@Kristen Barber Are you able to provide screenshots? There are quite a few moving parts with this one, so I would need to see exactly where the error is occurring before I can suggest any troubleshooting.

Melissa Dudley

@Paul Newcome - Hey Paul! Your template is great and exactly what I need. I'm going a #Blocked error and #Circular Reference error within the Stage 1 Check column. Below is a screenshot. I'm not sure what I'm doing wrong. Any help would be great appreciated!

Paul Newcome

What is in the cell giving the circular reference error?

ON

@Paul Newcome I'm having the same/similar issue where I get an error. I think it's because I copied your formula from the example and the formula is referencing "Row 2" of the "stage X check" column - which in the example is an "admin" row with numbers in a non-checkbox cell type... I don't have a way (that I'm aware of) to make a column that is checkboxes have a cell that isn't the same. My suggested "hack" is removing the cell ref altogether and replacing it with the valid value - "number in quotes"

Example given:

=IF($[ZZZ ANCESTOR]@row = VALUE(SUBSTITUTE($[Stage 1 Check]$2, "'", "")), 1)

The issue is: "$[Stage 1 Check]$2" in my sheet and the example screenshot above don't have the ref in the relevant cell.

My "hack" - remove the cell ref and enter the value in the formula directly - in this case "0":

=IF($[ZZZ ANCESTOR]@row = VALUE(SUBSTITUTE("0", "'", "")), 1)

ON

Image for the comment above... It references the cell in red... I just replaced the cell ref with the value "0"

ON

FYI - I managed to get it to work for me - and it's AWESOME!!!! Thank you :-)

Paul Newcome

@ON There in row two I entered

'0

apostrophe zero

This leaves a text value that looks like a number. Then we use the SUBSTITUTE function to remove the apostrophe and the VALUE function to convert it to a number.

If you replace the cell reference with a number, you can actually do away with both of those functions entirely.

=IF($[ZZZ ANCESTOR]@row = 0, 1)

The reason I did it the way that I did was so that I could easily dragfill/copy/paste formulas without having to edit them.

ON

@Paul Newcome And that is why you are a Smartsheet Jedi and I am not :-) That's brilliant, thank you for the reply... I will retry with the '0 and smart formula for drag to fill!

Paul Newcome

@ON Happy to help. 👍️

Erin Irick

Is this sheet still available? I was trying to access it and it says it is no longer published.

Paul Newcome

@Erin Irick This solution is actually rather obsolete.

This should get you started:

https://community.smartsheet.com/discussion/77827/work-breakdown-structure-wbs-column-formula-configuration