Is it possible to create uniquely identified numbers using level 1 parent tasks as a baseline?
Is it possible to pull the first three letters of a parent task, and then give it an unique identifying number xxx-9999?
I have groups of tasks that I would like to create an ID for whenever there is a newly created line, each line would inherently be a child to parent task. For example a subtask of an "Engineering" group might read:
Eng-0001
Right now I have the parents nested in a [Task] column, it would be great to have some logic surrounding if it is, or is not a level 1 parent, so it doesn't automatically number parent tasks.
Best Answers
-
Let's try this...
Insert an auto-number column (no special formatting needed).
Next insert a text/number column called "Row Number" with the following column formula:
=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)
Then we need to insert a text/number column we can call "Parent Helper" and use this column formula:
=LEFT(PARENT(Task@row), 3) + "-"
Now we can insert a text/number column to record your unique ID and start working out that formula. This should work for you:
=IF(COUNT(ANCESTORS(Task@row)) < 0, [Parent Helper]@row + IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 10, "000", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 100, "00", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 1000, "0"))) + COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row))
-
Sorry about that. I used the wrong argument in the very first IF.
=IF(COUNT(ANCESTORS(Task@row)) > 0, [Parent Helper]@row + IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 10, "000", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 100, "00", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 1000, "0"))) + COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row))
Answers
-
Let's try this...
Insert an auto-number column (no special formatting needed).
Next insert a text/number column called "Row Number" with the following column formula:
=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)
Then we need to insert a text/number column we can call "Parent Helper" and use this column formula:
=LEFT(PARENT(Task@row), 3) + "-"
Now we can insert a text/number column to record your unique ID and start working out that formula. This should work for you:
=IF(COUNT(ANCESTORS(Task@row)) < 0, [Parent Helper]@row + IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 10, "000", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 100, "00", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 1000, "0"))) + COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row))
-
This is really quite incredible Paul, you're a Wizard. I followed this the best I could. I now have columns that have all the nomenclature set up. The last formula though returned at first a #[Blocked] I believe is what it said, but when I refreshed it just disappeared and nothing is showing up in the final column. Would you happen to know what is causing this?
Really I can't thank you enough for the help! I've see you on other posts giving this level of detailed insight, it's downright awesome.
-
Sorry about that. I used the wrong argument in the very first IF.
=IF(COUNT(ANCESTORS(Task@row)) > 0, [Parent Helper]@row + IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 10, "000", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 100, "00", IF(COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) < 1000, "0"))) + COUNTIFS([Parent Helper]:[Parent Helper], [Parent Helper]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row))
-
An easy mistake to make! Thank you so much for the walkthrough on this.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!