Search code examples
excellistexcel-formulaproject

Create a dynamic numbered list based on Milestone/Tasks/Subtasks

I would like to create a formula based number list dependent on if a line item is classified as a Milestone, Task or Subtask.

The manual output would look as such:

1     Milestone
1.1   Task
1.1.1 Subtask

The formula needs to be dynamic enough to handle situations when there are multiple tasks (but no subtasks) or a scenario when numerous subtasks are created and a new task is created. Example below

1 Milestone
1.1  Task
1.2  Task
1.3  Task
1.3.1 Subtask
1.3.2 Subtask
1.3.3 Subtask
1.4   Task
2    Milestone

I have tried the following, which attempts to account for when a value is above 10:

=IF(B10="milestone",IF(A9<10,LEFT(A9)+1,LEFT(A9,2)+1),IF(B10="Task",IF(B9="Milestone",A9+0.1,IF(B9="subtask",IF(LEFT(A9,1)<=9,A9+0.1,LEFT(A9,4)+0.1),A9+0.1)),IF(B10="subtask",IF(B9="subTask",IF(NUMBERVALUE(LEFT(A9,1))<=9,LEFT(A9,4)&RIGHT(A9,1)+1,0),A9&"."&1))))

I have also tried:

=IF(B39="milestone",IF(A38<10,LEFT(A38)+1,LEFT(A38,2)+1),IF(B39="Task",IF(B38="Milestone",A38+0.1,IF(B38="subtask",IF(LEFT(A38,1)<=9,A38+0.1,LEFT(A38,4)+0.1),A38+0.1)),IF(B39="subtask",IF(B38="Task",IF(A38<=9,LEFT(A38,4)&"."&1,LEFT(A38,5)&"."&1),LEFT(A38,5)&RIGHT(A38,1)+1))))

In both instances I run into the a #Value! error.

Solution

  • With Office 365 Excel we can use TEXTSPLIT and TEXTJOIN to pull apart and put back together. Putting that with BYCOL we can change just the one we want:

    =LET(
    tp,B2,
    ref,A1,
    clms,MATCH(tp,{"Milestone","Task","Subtask"},0),
    TEXTJOIN(".",TRUE,BYCOL(SEQUENCE(,clms),LAMBDA(a,IFERROR(INDEX(TEXTSPLIT(ref,"."),0,a)+0,0)+IF(a=clms,1,0)))))

    Note that this refers to the row above it, so it needs to start in at least the 2nd row.

    enter image description here