Auto-Number Based on Parent Child Relationship + Move Rows in Work Breakdown Structure

Daniel Cote

Hello Community,

My company is trying to use a sheet to track all open eMarketing efforts each with a series of sub-tasks. I found a great solution within this community and is based on this: https://www.smartsheet.com/marketplace/templates/work-breakdown-structure-wbs , where I can have an ID for any added new row with a Task Name added that will also assign a suffix to the children for the Campaign#.

Here are my column formulas:

AutoNum

Prefix =PARENT() + IF([Parent ID]@row = "TOP", "", PARENT(Suffix@row) + "-")

Suffix =MATCH(UniqueID@row, COLLECT(UniqueID:UniqueID, [Parent ID]:[Parent ID], [Parent ID]@row), 0)

Parent ID =IF(COUNT(ANCESTORS()) = 0, "TOP", "R" + PARENT(AutoNum@row))

UniqueID =IFERROR([Task Name]@row + AutoNum@row, "")

Level =COUNT(ANCESTORS()) + 1

isParent (checklist) =IFERROR(IF(COUNT(CHILDREN()) > 0, 1, 0), "")

Campaign# ="eMKTG-" + IF(Prefix@row <> "", Prefix@row, "") + Suffix@row

My issue is this: if the order of the parent campaigns get moved up or down (meaning if I move row 8 to the top) then Campaign B (under Task Name column) will then become "eMKTG-1" when I want it to stay "eMKTG-2" with all of the correct 2-# suffixes.

Can anyone spot where I am going wrong?

(I've tried to use RANKEQ + COLLECT under Unique ID but then the Suffix column doesn't start with 1 for the first Sub task, instead starts with 7).

Any help would be greatly appreciated.

jmyzk_cloudsmart_jp

@Daniel Cote

=RANKEQ(AutoNum@row, COLLECT(AutoNum:AutoNum, [Parent ID]:[Parent ID], [Parent ID]@row), 1)

RANKEQ( number range [ order ])

• number — The number, or cell reference, of the value to rank
• range — The cell references, or range, to rank
• order —[optional] Specifies rank order: 0 (default) is descending order, and 1 is ascending order

https://help.smartsheet.com/function/rankeq

Daniel Cote

Hello @jmyzk_cloudsmart_jp I used this in the suffix column and it works as intended. Thank you for your help.

Hannah_Trousdale45

@jmyzk_cloudsmart_jp Is there any way I can create different sections of ordered numbers 1, 2, 3, etc. row then go back to 1, 2, etc. without indenting? In the WBS column, I would like it to go back to 1 in row 11 after the grey space.

jmyzk_cloudsmart_jp

Hi @Hannah_Trousdale45

Here is a solution.

https://app.smartsheet.com/b/publish?EQBCT=01422647fc024011b862ab4b4ed8bf82

[Parent Number] =IF([Skip WBS]@row, MATCH([Level Code]@row, COLLECT([Level Code]:[Level Code], [Skip WBS]:[Skip WBS], 1)))
[Parent Group] =IF(NOT([Skip WBS]@row), MAX(COLLECT([Parent Number]:[Parent Number], [Level Code]:[Level Code], <[Level Code]@row)))
[WBS] =IF(NOT([Skip WBS]@row), MATCH([Level Code]@row, COLLECT([Level Code]:[Level Code], [Parent Group]:[Parent Group], [Parent Group]@row)))
[WBS with Parent] =[Parent Group]@row + "-" + WBS@row

Hannah_Trousdale45

@jmyzk_cloudsmart_jp Why don't predecessors show up in Report sheets?

It should look like this (Sheet View):

jmyzk_cloudsmart_jp

Hi @Hannah_Trousdale45

Have you activated the dependencies?

https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors

Hannah_Trousdale45

@jmyzk_cloudsmart_jp Yes, my dependencies are activated in the other sheets. This is not a normal grid, it is a report. Does that make a difference?