MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Create a drop down list in Excel?

Posted by RRA on June 12, 2000 7:29 AM

I would like to create a drop down list of several items in one cell. Currently, I have many other formulas that populate based on the selection I just type in that one cell. However, it would be easier if I could create a drop down list, allowing the formulas to still work based on the selection from the list.

Thank you for your assistance,

Posted by Pooja on November 30, 2000 6:18 PM

How to make a drop down list as readonly


I have created a pull down menu for cell thru a VBA

Now I want to make that pulldown.....just readonly.
THe user can just see the options but he shouldn't
not be allowed to select any options.

How do I need to do that?

Please help me as soon as possible.

I have used..Selection.validation object.

But please tell me the various other properties
to be set to achieve this


Posted by JAF on June 12, 0100 8:26 AM

If you're using Excel 97 or later, you can use Data Validation (from the Data menu) to restrict input to items from a list that you define.

You can either type the list values directly into the validation box or specify a range on the worksheet where the list items appear.

This shouldn't have any impact on your existing formulas.


Posted by Garry on June 12, 0100 8:38 AM

If I understand your problem, you have for example, a list of fruit, Apple, Orange, Lemon, Lime and Pear in cells A1:A5, Cell B5 has been set with validation from the Tools menu to allow to allow selection from a drop down list, the source of which is A1:A5. You have many formulas, one of which may be in Cell C5 as:

@if(B5="Apple",1,@if(B5="Orange",2,@if(B5="Lemon",3,@if(B5="Lime",4,@if(B5="Pear",5,"No Fruit Today")))))

I have tried the above with success. Selecting from the drop down list in cell B5 has the desired effect on cell C5. If Apple is selected, then 1 is returned.

This being the case, I see no reason for other more useful and complicated formulas not to work.