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 xxx9999?
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:
Eng0001
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 autonumber column (no special formatting needed).
Next insert a text/number column called "Row Number" with the following column formula:
=MATCH([AutoNumber]@row, [AutoNumber]:[AutoNumber], 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 autonumber column (no special formatting needed).
Next insert a text/number column called "Row Number" with the following column formula:
=MATCH([AutoNumber]@row, [AutoNumber]:[AutoNumber], 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
Check out the Formula Handbook template!