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

Options
Daniel Cote
Daniel Cote ✭✭
edited 07/27/23 in Formulas and Functions

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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!