MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Drop Down List

Posted by Hamid on February 17, 2001 7:32 PM

I need to create a list in my worksheet that allows to select names from the list rather than having to type names. can you help.

Posted by Dave Hawley on February 17, 2001 8:31 PM

Hi Hamid

Sounds like you are refering to Validation. As you are wanting a drop down list I would recommend highlighting K1:K10 (or any other range) and the naming it "MyRange". I have included some extra rows in the range so that your Validation list will expand automatically if you happen to add more entries. You will see what I mean in a minute.

1. Select cell A1. Go to Data>Validation. Select "List" from the "Allow" box.

2. In the "Source" box type: =MyRange. Make sure the "Ignore Blank" and "In cell drop down" are checked.

3. Click the "Input message" tab and type a message if you want one to appear whenever A1 is selected.

4.Click the "Error alert" tab and type a message if you want one to appear IF a entry that is not part of the list is typed in the cell.

5. Set the error alert level from the "Style" box. Now click OK and we're done!

Select cell A1 and you can now pick from your list.
Type another entry in K4 and it too will become part of your list. This is why I suggested naming the range K1:K10.

You should be aware that Validation will NOT stop a user from pasting invalid data into A1. To stop this you would need some simple VBA code that I can supply if interested.

BTW. If you go to my web page and click "Handy Hints" you will see some other uses for Validations.

Hope this helps. If not just shout and I'll help.


OzGrid Business Applications

Posted by Hamid on February 18, 2001 11:56 PM

Thank you ;)