Auto-Number Based on Parent Child Relationship + Move Rows in Work Breakdown Structure
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
-
=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
-
Hello @jmyzk_cloudsmart_jp I used this in the suffix column and it works as intended. Thank you for your help.
-
@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.
-
Here is a solution.
[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 -
@jmyzk_cloudsmart_jp Why don't predecessors show up in Report sheets?
It should look like this (Sheet View):
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!