Dynamic, multi-level, numbered list using Excel formula

Folks,

I've managed to create a dynamic, multi-level, numbered list only using excel formula. This working correctly and I thought I'd share the result. My only question is if someone wants to take this and try to simplify it. I can't seem to upload an example sheet (first time posting).

Edit I'm using a table called tbOOA, with 2 columns, Choose Level and Result. To use the formula, copy each line of the coded section below that does not start with > and copy it into one single (long) forumula.

The desired result: Outcome 1

Output 1.1

Activity 1.1.1

Activity 1.1.2

Output 1.2

Activity 1.2.1

Outcome 2

Output 2.1

Activity 2.1.1 etc etc

>The formula explained:
>First choose if it is an Outcome, Output or Activity. If blank, then nothing
=IF([@[Choose Level]]="","",

>If it is an Outcome, put the word "Outcome" into column C
IF([@[Choose Level]]="Outcome", "Outcome " &

>and concatenate it with a count of the number of "Outcome" already chosen, limited from the header of the table, until this row
COUNTIF(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]], [@[Choose Level]]),

>Else, if "Output" is chosen, put the word "Output"
IF([@[Choose Level]]="Output", "Output "&

>and concatenate it with the number of "Outcome" from the table header to this line
COUNTIF(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]],"Outcome")

>and concatentate it with a dot and then
&"."&

>(This was the difficult part of the formula)
>count how many instances of "Output" there are between the last (most recent) instance of "Outcome" and the current row
COUNTIF(INDIRECT(ADDRESS(SUMPRODUCT(MAX(ROW(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]])*(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]]="Outcome"))),COLUMN([@[Choose Level]]))&":"&ADDRESS(ROW([@[Choose Level]]),COLUMN(([@[Choose Level]]))),TRUE),"Output"),

>Else, it must be an "Activity", so put "Activity" 
"Activity " &

>and concatenate with the number of "Outcome" from table header to this row, and dot
COUNTIF(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]],"Outcome")&"."&

>and count how many instances of "Output" between the last "Outcome" and this line
COUNTIF(INDIRECT(ADDRESS(SUMPRODUCT(MAX(ROW(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]])*(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]]="Outcome"))),COLUMN([@[Choose Level]]))&":"&ADDRESS(ROW([@[Choose Level]]),COLUMN(([@[Choose Level]]))),TRUE),"Output")&"."&

>and finally, count how many instances of "Activity" since the last "Output"
ROW([@[Choose Level]])-SUMPRODUCT(MAX(ROW(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]])*(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]]="Output"))))))

Solution

I can get it down to about half the size of yours.

Array formula**:

=CHOOSE(MIN(ROWS(INDEX([ChooseLevel],1):[@ChooseLevel]),4),"Outcome1","Output1.1","Activity1.1.1",[@ChooseLevel]&COUNTIF(INDEX([ChooseLevel],1):[@ChooseLevel],"Outcome")&IF([@ChooseLevel]="Outcome","",SUBSTITUTE("."&COUNTIF([@ChooseLevel]:INDEX([ChooseLevel],MATCH(1,0/(INDEX([ChooseLevel],1):[@ChooseLevel]="Outcome"))),"Output")&"."&COUNTIF([@ChooseLevel]:INDEX([ChooseLevel],MATCH(1,0/(INDEX([ChooseLevel],1):[@ChooseLevel]="Output"))),"Activity"),".0","")))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).