Override a list selection based on another cell value

[ToniB123]

I have a drop down list of 5 options to track case status in cell E1 of sheet 5 (active, paused, waiting, closed, declined). On sheet 6, cell F1, staff are asked to enter a closure date. I am sure they will forget to go back and change their case status selection to 'closed' so I would like E1 to override whatever the current selection is and enter 'closed' as soon as a date is entered in F1, sheet 6 but I am struggling to make this work with the data validation still turned on - any ideas?

 

[diddi]

hi and welcome to MrExcel
you may need to use a worksheet_change macro here, because sheet formulas would get confused changing selections in a dropdown.
What is the name of your drop down box on sheet 5

 

[ToniB123]

hi and welcome to MrExcel
you may need to use a worksheet_change macro here, because sheet formulas would get confused changing selections in a dropdown.
What is the name of your drop down box on sheet 5

thanks, the drop down is 'status'

 

[diddi]

do you know how to get to the VBA window? if yes, then place this in the code for sheet 6

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$1" Then
        If IsDate(Target.Value) Then
            Sheets("Sheet 5").Cells(1, 5) = "closed"
        End If
    End If
End Sub