Data Validation Macro to hold/keep entries individually when making new selection from parent cell.

clutterbug If I've understood correctly, you want to input values for January-2024, then change to another month using the data validation list in cell A1, input values for that month, then switch back to January-2024 and have it bring back all of your previous entries for January? In order to accomplish this, you'll need to store the data somewhere... there's basically two approaches you could take:

The first would be to have a separate data entry worksheet with a table to hold all of the employee attendance records for the entire year, then use the worksheet you've created as the "output" or "report" with lookup formulas to pull in the applicable data for each month.

The second would be to have 12 rows for each employee (one for each month) to input data directly on the worksheet you've created, then filter the data by month (column A) when you change the month in cell A1 (instead of clearing the data). For example, the setup would look something like this:

Setup: monthly rows for each employee

Note: In this example, I've used the first day of each month in column A, which is consistent with my data validation list in cell A1. If your data validation list uses the last day of each month, be sure follow suit with the dates in column A.

Also: when using Date Filters > Equals to filter by date, you MUST apply the default "Short Date" cell formatting to the range of dates being filtered. Custom formatting can be applied to the criteria date in cell A1, but NOT to the range of cells being filtered in column A.

Next, the VBA code to filter the data by column A when the value in cell A1 changes would be:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Apply filter if the criteria cell was changed
    Dim rg As Range
    Set rg = Me.Range("A1")
    If Not Intersect(Target, rg) Is Nothing Then
        Dim rgData As Range, lastRow As Long
        lastRow = Me.Cells(Me.Rows.Count, 1).End(xlUp).Row
        Set rgData = Me.Range("A4:A" & lastRow)

        If rg.Value = vbNullString Then
            rgData.AutoFilter Field:=1
        Else
            rgData.AutoFilter Field:=1, Criteria1:="=" & rg.Value, _
                Operator:=xlOr, Criteria2:="="
        End If
    End If
End Sub

Now you can switch back and forth between months to view/edit the employee attendance records:

Filtered by January-2024 (01/01/2024)

Filtered by February-2024 (02/01/2024)

You could also move the data validation picklist to cell B1, for example, so you can hide column A for aesthetic reasons.

I hope that helps. Cheers!