If this = this, then perform this action

I have attached a workbook for better reference...

Here is what I am trying to do...

I have 3 worksheets in the workbook. One for First shift employees, one for Second shift employees and a third for Terminated Employees.

Each day when I do attendence, I use a drop down menu to show if the employees are here, called off, ncns, late, termed etc...each work book is for a week of data. So it shows Monday - Sunday. If in one of those fields, I enter the "termed" option, I would like it to automatically populate the employees name, start date, end date (the date it shows "termed"), and the shift worked in the third workseet "Termed".

So, on the third Sheet it would show Jess Donaldson from first shift was termed on Nov 18 and Harold Bigler from second shift was termed on Nov. 19.

Is this even possible? Does someone have any ideas of how I would go about doing this?

If anyone has any thoughts, suggestions, advice, I would really appreciate it.

Just a thought, not even halfway to a solution, do you have to have the data for the different shifts on separate worksheets?
I would have thought it easier to have a single sheet for your baseline data, with an extra column to indicate which shift the person was engaged in. You can always use other methods to show/ print out reports for the separate shifts, if that information is required separately.

You could use a VBA solution.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NextRw As Long
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("F3:J72")) Is Nothing And Target.Value = "Termed" Then
        With Worksheets("Termed")
            NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(NextRw, 1).Value = Me.Cells(Target.Row, 5).Value
            .Cells(NextRw, 2).Value = Me.Cells(Target.Row, 2).Value
            .Cells(NextRw, 3).Value = Me.Cells(2, Target.Column).Value
            .Cells(NextRw, 4).Value = Me.Name
        End With
    End If
End Sub

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste


Where does the combobox issue in your title occur?

The combobox should really be a separate question.

1. It doesn't include Transfer because the range formula is wrong & that cell is excluded. I'm not sure why you want a Dynamic Named Range, but the formula should be

='1st Shift'!$E$76:OFFSET('1st Shift'!$E$76,COUNTA('1st Shift'!$E$76:$E$65536)-1,0)

2. Once you have entered a term once in the column using the Data Validation it will auto-fill subsequently. I don't think the effort of adding comboboxes is wort hit, although there is a workaround here

Please use Code Tags in future.

The code fills in Name, Start Date, End Date & Shift

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NextRw As Long
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("F3:J72")) Is Nothing And Target.Value = "Termed" Then
        With Worksheets("Termed")
            NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            'name
            .Cells(NextRw, 1).Value = Me.Cells(Target.Row, 5).Value
            'start date
            .Cells(NextRw, 2).Value = Me.Cells(Target.Row, 2).Value
            'end date from the column header of the cell
            .Cells(NextRw, 3).Value = Me.Cells(2, Target.Column).Value
            'shift from the sheet name
            .Cells(NextRw, 4).Value = Me.Name
        End With
    End If
End Sub

I've also modified the range formula

Thank you so much for your help, I am not sure why I was having issues with this initially but it seems to be working seamlessly now.