Multilevel Numbering automation in excel

Maybe :

Sub Test()
  Dim coll As New Collection, rngResult As Range, arrIn(), arrOut(), iRow As Long, i As Long, j As Long, v
  With Range("A6:A" & Application.Max(Cells(Rows.Count, "A").End(xlUp).Row, 6))
    iRow = .Row - 1
    arrIn = .Value
    For i = 1 To UBound(arrIn, 1)
        On Error Resume Next
           coll.Add key:=arrIn(i, 1), Item:=New Collection
        On Error GoTo 0
        j = InStrRev(arrIn(i, 1), ".")
        If j Then coll(Left$(arrIn(i, 1), j - 1)).Add iRow + i
    Next i
    ReDim arrOut(1 To UBound(arrIn, 1), 1 To 1)
    For i = 1 To UBound(arrOut, 1)
        Set rngResult = Nothing
        If coll(arrIn(i, 1)).Count Then
           For Each v In coll(arrIn(i, 1))
               If rngResult Is Nothing Then Set rngResult = Range("B" & v) Else Set rngResult = Union(rngResult, Range("B" & v))
           Next v
           arrOut(i, 1) = "=MAX(" & rngResult.Address(0, 0) & ")"
        End If
    Next i
    .Offset(, 3).Formula = arrOut
  End With
End Sub

You are welcome.

In your example file, you don't put the effect of rule 3 : Additional check will be on Column C ("Relevant"),
so I will set the value of column G to "NO" for column C = "No".

Sub Test()
  Dim coll As New Collection, rngResult As Range, arrIn(), arrOut(), iRow As Long, i As Long, j As Long, strFormula As String, v
  strFormula = "=(VLOOKUP(V1,Sheet2!$A$2:$B$4,2,FALSE)+VLOOKUP(V2,Sheet2!$A$2:$B$4,2,FALSE)+VLOOKUP(V3,Sheet2!$A$2:$B$4,2,FALSE))"
  With Range("A2:C" & Application.Max(Cells(Rows.Count, "A").End(xlUp).Row, 2))
    iRow = .Row - 1
    arrIn = .Value
    For i = 1 To UBound(arrIn, 1)
        On Error Resume Next
           coll.Add key:=arrIn(i, 1), Item:=Array(arrIn(i, 1), New Collection)
        On Error GoTo 0
        j = InStrRev(arrIn(i, 1), ".")
        If j Then coll(Left$(arrIn(i, 1), j - 1))(1).Add i
    Next i
    ReDim arrOut(1 To UBound(arrIn, 1), 1 To 1)
    For i = 1 To UBound(arrOut, 1)
        If arrIn(i, 3) = "Yes" Then
           Set rngResult = Nothing
           If coll(arrIn(i, 1))(1).Count Then
              For Each v In coll(arrIn(i, 1))(1)
                  If rngResult Is Nothing Then Set rngResult = Range("G" & v + iRow) Else Set rngResult = Union(rngResult, Range("G" & v + iRow))
              Next v
              arrOut(i, 1) = "=MAX(" & rngResult.Address(0, 0) & ")"
           Else
              arrOut(i, 1) = Replace$(strFormula, "V1", "D" & i + iRow)
              arrOut(i, 1) = Replace$(arrOut(i, 1), "V2", "E" & i + iRow)
              arrOut(i, 1) = Replace$(arrOut(i, 1), "V3", "F" & i + iRow)
           End If
        Else
           arrOut(i, 1) = "NO"
        End If
    Next i
    .Resize(, 1).Offset(, 6).Formula = arrOut
  End With
End Sub

Hard to tell without seeing the actual data, including your code.
Please post the workbook (including the code).

You are welcome.

Originally Posted by kimjaggi88

The last question I have is- is it possible to automate the creation of numbering in Tree Structure and if not then atleast sort them if something is not in place..

Unless there is identifiable pattern on the data, this should be done manually.
For example, if on level 1 of tree structure there is word "region", on level 2 there is word "subregion", on level 3 is the rest of data which doesn't have words "region" and "subregion" like this :
North region
A subregion
XXX1
XXX2
B subregion
XXX3
South region
C subregion
XXX4
D subregion
XXX5
XXX6
If there is a pattern like this, the automation is possible, if there isn't then it is impossible.