How to allow custom entry to cell based on a specific condition with list condition?

I had a cell that applied with list validation. The user can choose Option 1, Option 2, Option 3, Others.

Setting

What I want is that if Others is being selected, then the user is allowed to customs entry. Else if the cell is empty or with Options 1,2,3, the user is not allowed to free text.

When doing the setting, I did not on the "Show error alert after invalid data is entered". However, this will allowed the user to free text at any time.

If I am on the "Show error alert after invalid data is entered", the user will not be able to free text. They could only choose the options given, which are Options 1,2,3 & Others.

Any suggestions and help are appreciated.

Based on what best I have understood your question. I am suggesting a tentative VBA solution. The code is created in Excel 2013. Should work in Excel 2016 as well. Do try this out and revert back if this works for you.

In this example Cell D3 is used. First create your validation manually in cell D3 the Choices exactly are Option 1, Option 2, Option 3 and Others.

Now access VBA Editor by pressing ALT + F11. VBA Code editor window shall open. Double click on Excel Object Sheet1 for example if that's your intended sheet and in right code window select Worksheet from First Drop Down and Change event from next drop down.

Put the following VBA code into it. Between Private Sub Worksheet_Change(ByVal Target As Range) and End Sub

If Target.Address = "$D$3" Then
    If Target.Value = "Others" Then
        With Target.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End
    End If
End If

If Target.Address = "$D$3" Then
  With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Option 1,Option 2,Option 3,Others"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
  End With
End If

Save the file as .xlsm and test the way the validation now works in Cell D3.

After analyzed your Query I came to conclusion that you need Conditional Data Validation. For this you need to follow few steps.

Step 1: Select the Cell where you want to put the Choice and select Data Validation command then Setting, List and in Source Textbox write the Key Words. Like you have already listed.

Step 2: You need to create Named Data Range for Key Words like Option1 and others. Remember for "Others" keep the Data Range BLANK but other has to be populated with data.

Step 3: Select the Cell which you want to validate for your entries according to Key Words, start with Data Validation Command then Settings next set List option and finally write the following Formula.

=IF(A2="Option1",OptionOne,IF(A2="Option2",OptionTwo,If(A2="Option3",OptionThree,Others)))

NB: Please note Option1, Option2 & Option3 are the Name I've given to the Key Words. And OptionOne, OptionTwo & OptionThree are the Column Headings for the Named Data Ranges.

I've suggest you to keep the Data Range Others BLANK Because those blank cells allow you to write Free Data.

Hope this will help you. Remember I've posted this solution after is been TESTED by me.