Issue with LIST values in excel drop down

koradaramjee 

Yes, there are some flaws with data validations.

To handle this scenario you can apply a conditional formatting rule in the column A so that if a value is entered somehow which is not available in the dropdown list would highlight the cell say with red color to visually alert the user that an invalid value is there in the cell.

Or other workaround is VBA which can stop this to take place and only values in the dropdown can be entered in the column A.

Please refer to the attached with the following Change Event code on Sheet1 Module which will only allow a user to choose a valid entry only from the dropdown list in a cell if that cell has a dropdown in it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Variant
Dim cel As Range
Dim tbl As ListObject
Set tbl = ListObjects("Table1")
If Target.Column = 1 And Target.Row > 1 Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each cel In Target
        On Error Resume Next
        n = cel.Validation.Type
        On Error GoTo 0
        If n = 3 Then
            If IsError(Application.Match(cel.Value, tbl.DataBodyRange.Columns(1), 0)) Then
                cel.Value = ""
            End If
        End If
    Next cel
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End If
End Sub