|This suggestion assumes your Drop Down is in A1. Adjust the code as required.|
Right click the sheet tab for the sheet with the Drop Down.
Choose View Code
Paste this into the window that opens:
Private Sub Worksheet_Change(ByVal Target As Range)
'If Drop Down choice is Other, get value
'from user and place it in A1
If Target.Address = "$A$1" Then
If Target = "Other" Then
otherVal = Application.InputBox("Enter Other Value")
If otherVal = False Then Exit Sub
Range("A1").Value = otherVal
When you choose Other, you be given the chance to enter your own value. If you Cancel out of the input box, Other will remain in the Drop Down.
Keep in mind that while A1 contains Other, the formula that references A1 will probably show an error.
You can deal with that via an IF statement:
=IF(A1="Other","",your actual formula)