Auto-Number Based on Parent Child Relationship + Move Rows in Work Breakdown Structure
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:
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.
Help Article Resources
Check out the Formula Handbook template!