Fill a sheet with data from different sheets same workbook in a very tricky way - Vol 2

[kelly mort]

Cross posted at:

Fill a sheet with data from different sheets same workbook in a very tricky way - Vol 2

This post is an upgrade for the request at: https://www.excelforum.com/excel-programming-vba-macros/1303122-fill-a-sheet-with-data-from-three-different-sheets-same-workbook-in-a-very-tricky-way.html of which @Marc L provided a great solution for a while ago. Private Sub...

www.excelforum.com

Reason:
To place my problem before more experts

.......................................................................................
.......................................................................................
This post is an upgrade for the request at:
https://www.excelforum.com/excel-pro...ricky-way.html

of which @Marc L provided a great solution for a while ago.

Link to sample workbook:

DEMO_WORKBOOK.xlsm

Shared with Dropbox

www.dropbox.com

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim K, H, L, N&, V, R&, C%
    If Target.Address <> "$B$1" Then Exit Sub
    Application.EnableEvents = False
    Me.UsedRange.Offset(1).Clear
    If IsEmpty(Target) Then Application.EnableEvents = True: Exit Sub
    K = [{2,3,4,15}]
    H = Application.Index(Worksheets(1).UsedRange.Rows(1), , K)
    L = Application.Index(Worksheets(1).UsedRange.Rows(1), , [{16,16,17,17}])
   
    For N = 1 To Me.Index - 2 '1
        With Sheets(N).UsedRange
            V = Application.Match(Target, .Columns(1), 0)
            If IsNumeric(V) Then
                Cells(R + 2, 1).Value2 = .Parent.Name
                Cells(R + 3, 1).Resize(, UBound(K)).Value2 = H
                R = R + 4
                Cells(R, 1).Resize(, UBound(K)).Value2 = Application.Index(.Rows(V), , K)
                For C = 5 To 13 Step 2
                    If IsEmpty(.Cells(V, C)) Then Exit For
                    R = R + 1
                    Cells(R, 2).Resize(, 2).Value = .Cells(V, C).Resize(, 2).Value
                Next
                R = R + 1
                L(2) = .Cells(V, 16).Value2:  L(4) = .Cells(V, 17).Value2
                Cells(R, 1).Resize(, UBound(L)).Value2 = L
            End If
        End With
    Next
    Application.EnableEvents = True
End Sub

I have added additional sheet with the name "EXTRA_DATA". And what this sheet does is recording extra entries in case the 5 slots on any of the sheets, "GROUP " 1 to 3 are exhausted.

And for that matter, the column, "GROUP" on the "EXTRA_DATA" sheet keeps track of the various groups as shown on the sample data sheet.
Then the "YEAR" column also records the particular year that record is linked to (There may be multiple years).

So, with the two columns added, I am looking for a way that I can get the data (from "EXTRA_DATA" sheet merged or attached to the right or corresponding group on the output sheet as shown on the "OUT_PUT_DATA" sheet (NEW REQUEST).

I have been staring at the script for a while now and the only meaningful thing I was able to do was to change :

 For N = 1 To Me.Index - 1

to

 For N = 1 To Me.Index - 2

Which of course produced a result (which didn't get me to smile either).

Could someone please help me out?

Thanks in advance