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.