CODEWITHSUNDEEP
excel-formula
Cameron Smith
Cameron Smith

Reputation: 15

Creating a list of headings, sub-headings and sub-subheadings in Excel

I am looking to make a list of heading type 1, sub-heading type 2 and sub-sub-heading type 3, and each subsequent instance of a heading increments in excel. e.g.

Outcome 1
Output 1.1
Activity 1.1.1
Activity 1.1.2
Output 1.2
Activity 1.2.1
Activity 1.2.2
Activity 1.2.3
Outcome 2
Output 2.1
Activity 2.1.1
etc

Here is my formula - getting to be a complicated nested IF statement:

IF([@Column1]="","",
IF([@Column1]="Outcome", "Outcome " & COUNTIF(tbOOA[[#Headers],[Column1]]:[@Column1], [@Column1]), 
IF([@Column1]="Output","Output "& COUNTIF(tbOOA[[#Headers],[Column1]]:[@Column1],"Outcome") ***&"."&*** COUNTIF(tbOOA[[#Headers],[Column1]]:[@Column1],[@Column1]), 
"Activity " & "serious help")))

In Column 1, choose from a list of 'Outcome', 'Output', or 'Activity'.

In column 2, calculate the appropriate number, e.g. Output 1.2

If the row is empty, then nothing. - Fine

If it is "Outcome", count from the header until current row for the number of instances of "Outcome". - Fine    
Else if it is "Output", count the number of "Outcome"s there are. - Fine    
This is where it falls apart. Trying to calculate the number after the "." (bold and italic)

I need to count the # of instances of "Output", but then this has to reset to 1 each time there is a new 'Outcome'.

The logic I'm trying to follow is:

(# of "Outputs" from the table header until the current row) minus 
(# of "Outputs" from the table header until the last instance of "Outcome")

I've tried several attempts at calculating row number, but everything has been problematic.

The logic is the same for activities, though will complicate the formula even more and I haven't bothered to start on that until I can get level 2 sorted.

Does anyone know of a similar problem/solution?

Upvotes: 1

Views: 4549

Answers (2)

Pablo Straub
Pablo Straub

Reputation: 101

I had a similar problem, where I wanted to create the multi-level heading numbers based on the indentation of list of texts. So the numbers must be generated automatically with a user-defined formula (UDF) like below:

Computing next multilevel number based on indentation

For this to work, you must type ="1" in cell A2. The same formula in A3 (below) must be copied down to A4:A14.

=NextLevelNum(A2;IndentLevel(B3))

Function IndentLevel I took from https://professor-excel.com/how-to-return-the-indentation-of-a-cell-in-excel/

Function NextLevelNum I did myself. All code below.

Option Explicit

Public Function IndentLevel(Ref As Range) As Long
  Application.Volatile
  IndentLevel = Ref.IndentLevel
End Function

Public Function NextLevelNum(prevNumRef As Range, level As Integer) As String
  Dim prevNum As String
  Dim nums() As String
  Dim prevLevel As Integer

  prevNum = prevNumRef.Value
  nums = Split(prevNum, ".")
  prevLevel = UBound(nums) + 1

  ' Ensure  1 <= level <= prevLevel +1
  level = WorksheetFunction.Max(level, 1)
  level = WorksheetFunction.Min(level, prevLevel + 1)

  ReDim Preserve nums(0 To level - 1)

  If level = prevLevel + 1 Then
    nums(level - 1) = "1"
  Else
    nums(level - 1) = CStr(CInt(nums(level - 1)) + 1)
  End If

  NextLevelNum = Join(nums, ".")
End Function

Upvotes: 0

Ben I.
Ben I.

Reputation: 1082

If you are open to using hidden helper columns, the formulas become much more manageable. Use Column A to hold your "Outcome", "Output", and "Activity" data.

Then use column B to deal with Outcome numbers, column C to deal with Output numbers, and column D to deal with Activity numbers. Merge the final results together in Column E.

In B1, C1, and D1, manually write in the first values (1, 0, and 0).

Then, fill down starting from B2 with the following:

=IF(A2="Outcome",B1+1,B1)

This works by incrementing only if you have found your next Outcome.

Fill down from C2 with the following:

=IF(A2="Outcome",0,IF(A2="Output",C1+1,C1))

This works by incrementing only if you have found your next Output. It resets to 0 if you have a new Outcome.

Then fill down from D2 with

=IF(OR(A3="Outcome",A3="Output"),0,IF(A3="Activity",D2+1,D2))

It's very similar to the prior formula, but resets on an Outcome or an Output.

Finally, in D4, merge it all together with

=B1&IF(C1>0,"."&C1&IF(D1>0,"."&D1,""),"")

& is a string concatenate operation. By checking if the inner values are 0, we only concatenate . and the next number if the next number is non-zero.

Upvotes: 1

Related Questions