Auto-Number Using Parent() and Child() Relationship

Michael Murphy

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.

1. 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.
2. 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?
3. 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.

Thank you.

Stefan

Hi Michael,

you could use the PARENT function as a helper in the column LEVEL.

Use the PARENT functioning the LEVEL column to check for the parent of the current row. Level 1 rows will return no result. Use an IF formula in a the newly created TASK NR column to check if the LEVEL column in this row is empty and if yes show the number from your TASK NO column. Hide the original TASK NO (guess you use auto numbering from smartsheet) and the LEVEL column.

This does not care for multiple levels of indentation but with some additional conditions this could work in those cases too.

Hope this helps

Stefan

Stefan

Hi Michael,

you could use the PARENT function as a helper in the column LEVEL.

Use the PARENT functioning the LEVEL column to check for the parent of the current row. Level 1 rows will return no result. Use an IF formula in a the newly created TASK NR column to check if the LEVEL column in this row is empty and if yes show the number from your TASK NO column. Hide the original TASK NO (guess you use auto numbering from smartsheet) and the LEVEL column.

This does not care for multiple levels of indentation but with some additional conditions this could work in those cases too.

Hope this helps

Stefan

Michael Murphy

Thanks for the suggestion Stefan! I'll give that some thought when I have a bit more time. I think for now, I will assign every task its own task number to get things up and running. I'm not a big fan of adding "helper columns" to supplement lack of functionality.