MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Customer List

Posted by Mary Beth on February 22, 2001 11:23 AM

Help!!! I have a customer list I would like to be able to choose from by selecting from a drop down list but I can't figure out how to do it and still be able to type in a new customer if they aren't on the list. Any help would be MUCH appreciated!

Posted by Aladin Akyurek on February 22, 2001 11:29 AM



Read DDList as "CustomersList".


Posted by Mary Beth on February 22, 2001 12:52 PM

This was a great help in my getting a drop down list in my workbook but it still will not allow me to add to my list automatically...

Posted by Aladin Akyurek on February 22, 2001 1:48 PM

If you succeeded to create a dynamic range, say CustomersList, for customer values and you extend the list of customers by adding new ones, the added customers will be automatically a part of CustomersList. You can also use this name in formulas when needed.


Posted by David Hawley on February 22, 2001 8:59 PM

Try this. as you will see there is no real need for a Dynamic range with Validation.


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.

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

Hope this helps. If you wish for new names to be typed directly in the cell housing Validation, then you will need a macro ?


OzGrid Business Applications