Auto-Number Using Parent() and Child() Relationship
I have a sheet our team uses to track tasks/issues that need resolution as we move through project execution. Initially, I set the sheet up with a column to auto-number every new task/issue entry. This way the task/issue can easily be referenced by its number and no one has to waste time searching for text in the "Task" column.
With this in mind, I have the following scenarios/questions.
- There are occasions when a task/issue has multiple "parts" to it. I would like the sheet to automatically recognize that as I indent, the contents of that line should no longer have its own unique "Task No". Those "sub tasks" would roll up under the Parent task. As the text within the "Task" column gets indented, I want the corresponding "Task No." to be deleted. Then the next row (or new entry) would continue auto-numbering based on the previous "Task No." regardless of whether or not the previous "Task No." has associated CHILDREN.
- I have added a "Level" column to aid with reporting later on. Is there a way to use PARENT() to put an actual number in the Level column? For instance, the first indentation would be "Level 1", the second indentation would be "Level Two" and so on? This way later on I can sort by "Level" in a report?
- Is it possible the above scenario can be employed when using the form associated with the sheet (vs. manual sheet entry) to submit new tasks?
For example, after indenting the text in the "Task" column for Task No.s 0004 thru 0009, I would like the value in the "Task No." cell for those rows to be deleted. The result would then look like the second screen shot below.
This is the result I am hoping to achieve without having to manually adjust values in the respective cell of the "Task No." column.
Help Article Resources
Check out the Formula Handbook template!