In Column A of my excel sheet I'm trying to create a unique, sequential ID in each row according to the 'activity type' in Column C. Activity Title is in Column B, but the two relevant columns for this example are Column A and Column C. There are three activity types: Milestones, Tasks, and Subtasks.
A 'Milestone' is the largest body of work and can have several tasks and subtasks aligned to it. A 'Task' falls beneath a milestone and can have several subtasks aligned to it. And finally, a 'Subtask' is the smallest body of work and will not have anything aligned to it, but there can be several subtasks. Additionally, there is no limit to the number of 'Tasks' that fall beneath one milestone, and no limit to the number of 'Subtasks' that fall beneath one 'Task.
Thanks to some help, I've successfully formatted Column A using the formula below to show in the format listed below:
Formula:
Output:
However, I realized that if there are 10+ tasks or subtasks, it would reflect the nearest number (i.e. the task following task ID 1.9 would show as 2, which would imply a new milestone), so I'm hoping to adjust the formula from above and have it reflect a second decimal point to account for 10+ tasks and subtasks (For example 1.11.12). My thinking is that this change would allow for up to 99 tasks and subtasks before it rolls over (and there is no chance there would be that many tasks beneath one milestone or subtasks beneath one task).
I've included an example below for the ideal future state:
Any help or guidance provided is so appreciated!
A 'Milestone' is the largest body of work and can have several tasks and subtasks aligned to it. A 'Task' falls beneath a milestone and can have several subtasks aligned to it. And finally, a 'Subtask' is the smallest body of work and will not have anything aligned to it, but there can be several subtasks. Additionally, there is no limit to the number of 'Tasks' that fall beneath one milestone, and no limit to the number of 'Subtasks' that fall beneath one 'Task.
Thanks to some help, I've successfully formatted Column A using the formula below to show in the format listed below:
Formula:
Excel Formula:
=IFERROR(IF(C3="Milestone",FLOOR(A2,1)+1,IF(C3="Task",FLOOR(A2,0.1)+0.1,A2+0.01)),1)Output:
| ID (Column A) | Activity Name (Column B) | Activity Type (Column C) |
| 1 | X | Milestone |
| 1.1 | X | Task |
| 1.2 | X | Task |
| 1.21 | X | Subtask |
| 1.22 | X | Subtask |
| 1.3 | X | Task |
| 2 | X | Milestone |
| 2.1 | X | Task |
| 2.11 | X | Subtask |
| 2.12 | X | Subtask |
However, I realized that if there are 10+ tasks or subtasks, it would reflect the nearest number (i.e. the task following task ID 1.9 would show as 2, which would imply a new milestone), so I'm hoping to adjust the formula from above and have it reflect a second decimal point to account for 10+ tasks and subtasks (For example 1.11.12). My thinking is that this change would allow for up to 99 tasks and subtasks before it rolls over (and there is no chance there would be that many tasks beneath one milestone or subtasks beneath one task).
I've included an example below for the ideal future state:
| ID (Column A) | Task Name (Column B) | Activity Type (Column C) |
| 1 | X | Milestone |
| (Intermediary rows of data implied) | X | ... |
| 1.09.09 | X | Subtask |
| 1.09.10 | X | Subtask |
| 1.09.11 | X | Subtask |
| 1.10 | X | Task |
| 1.10.01 | X | Subtask |
| 1.10.02 | X | Subtask |
| 1.11 | X | Task |
| 1.11.01 | X | Subtask |
Any help or guidance provided is so appreciated!
