MrExcel Publishing
Your One Stop for Excel Tips & Solutions

drop down list


Posted by Fred King on February 14, 2001 4:08 PM

I want to create a drop down list of values in a cell and when the user selects a certain value, it is displayed in that particular cell. I have seen this done. Any help?

Thanks - Fred


Posted by Dave Hawley on February 14, 2001 5:18 PM

Hi Fred

Sounds like you are refering to Validation. As you are wanting a drop down list I would recommend highlighting K1:K10 (or whatever) and then 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.

Dave


OzGrid Business Applications