Forum Discussion

clutterbug's avatar
clutterbug
Copper Contributor
Jan 05, 2024

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

Hi all,

 

I'm working on creating an Employee Attendance Calendar, that has dependent data validation drop-down list in each cell referring to the employee & day of the week, then calculates the items selected under the respected Absence Reason columns. I was able to get as far as creating the Macro to reset the cells when selecting a new Month from cell A1. But I can't seem to find an answer anywhere on how to keep my entries that I already made when switching the Month back & forth. I would like for it to hold values based on each individual data validation list option.

 

Any help is much appreciated! I've been working on this for a week & decided it's about time to reach out to someone for some help. Thanks!Main_idea

 

Data Validation list of Months in cell A1

 

Resets cells in Month of Feb

 

The_Problem - removes my entries that I don't want to be resetted.

 

Macro_code to reset the table options

 

excel
Formulas and Functions
Macros and VBA

1 Reply

Sort By
  • djclements's avatar
    djclements
    Bronze Contributor
    Jan 06, 2024

    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!

Resources