Variable serial numbers in non adjacent cells

This is definitely a little more tricky than your previous question :-) I suppose it could be done with formulas, too but that's beyond my pay grade. So I offer VBA.

Option Explicit

Private Enum Nws                ' worksheet navigation (change values to suit)
    ' 103
    NwsFirstNumberedRow = 3     ' row above this one can't be blank (headers)
    NwsRowsPerDataSet = 3
    NwsNumber = 1               ' Columns:- 1= A, 2=B, 3=C etc
    NwsDate                     ' (no value = previous + 1)
End Enum

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 103

    Dim Arr         As Variant          ' values to paste to column NwsNumber
    Dim n           As Integer          ' NextGroupSize
    Dim Rl          As Long             ' last used row in NwsNumber
    Dim i           As Integer          ' loop counter: groups

    With Target
        If .Cells.CountLarge = 1 Then   ' skip if more than 1 cell was changed (paste)
            Rl = Cells(Rows.Count, NwsNumber).End(xlUp).Row
            If .Address = Cells(Rl + 1, NwsDate).Address Then
                n = NextGroupSize(Rl)
                ReDim Arr(1 To n * NwsRowsPerDataSet)
                For i = 0 To n - 1
                    Arr(i * NwsRowsPerDataSet + 1) = i + 1
                    Arr(i * NwsRowsPerDataSet + 2) = "Name"
                    Arr(i * NwsRowsPerDataSet + 3) = "Roll#"
                    'add more rows here if and when needed
                Next i
                With Cells(.Row, NwsNumber).Resize(UBound(Arr))
                    .Value = Application.Transpose(Arr)
                    ' you can format the cells here
                    .HorizontalAlignment = xlCenter
                End With
            End If
        End If
    End With
End Sub

Private Function NextGroupSize(ByVal Rl As Long) As Integer
    ' 103
    ' return the number of entries in the next group

    Dim Fun         As Integer          ' function return value
    Dim R           As Long             ' row calculator

    If Rl <= NwsFirstNumberedRow Then Rl = 0
    R = NwsFirstNumberedRow
    Do
        Fun = Fun + 1
        R = R + ((Fun + 1) * NwsRowsPerDataSet) + (Fun = 1)
    Loop While R <= Rl

    NextGroupSize = Fun + 1
End Function

Paste the entire code in the code sheet belonging to the worksheet on which you want the action. That's a sheet set up by Excel so that it is informed of worksheet events (standard modules you can add yourself don't have thast feature). The code responds to the Change event which occurs wqhenever a cell in the sheet is modified.

Now, you don't want a big halluballu whenever you change just anything on the sheet. But when you enter a date (anything, in fact, but you could add a test to be sure it's a date) in the cell in the Date column in the row below the last used cell in the Numbers column, then - and only then - you want numbers to be added. This means that at any given moment only a single cell on the entire sheet meets the criteria. On the attached workbook I have highlighted the cells on the TXL_4740 tab. Try your luck with B18.

I converted your hypothetical task into a generic one because hypothesis isn't practical. Please study the Enum[erations] section at the top of the code until you fully understand it. Basically, an enumeration is just a simple way of assigning names to numbers. But it's your control panel where you can assign/change columns and default rows. My example starts in row 3 (NwsFirstNumberedRow = 3). I doubt that your really starts in row 1 but you can choose any row. Just assign another number to the name. You can even add rows to each group in the future, like (Name, First name, Roll#).

Most of my time went into the function NextGroupSize, by the way. It looks innocent but it's loaded with complications.