My own drop-down list in Excel

Asus Eee pc 1005ha blue netbook
May 11, 2010 at 05:45:40
Specs: Windows XP Pro, 2096
Hello everyone!

The drop-down list of valid entries may be compiled from cells elsewhere on the worksheet. But I'd like to have the list of e.g. 4 positions where the last one would be "other" (--> 10,15,20,"other") whereas cliking on it I could type in any value that would be then calculated by the formula.

How is it possible?

Thanks a lot in advance for any valuable tip!


See More: My own drop-down list in Excel

Report •

May 11, 2010 at 07:28:43
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
   End If
 End If
End Sub

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)

Report •

May 11, 2010 at 22:51:45
Derby Dad03, that's great!

Thanks a lot again, have a nice day!


Report •

May 11, 2010 at 23:41:54
You're welcome.

Glad I could help.

Report •

Related Solutions

Ask Question