Data Validation list from another sheet

Hi everyone,c

I am trying to make drop down list with names from another worksheet.
Table with data which is located in different sheet which is imported from access database, it contains columns with names, address and phone numbers.
I want to be able to choose a name which will follow by automatic filling in the cells with the address and telephone numbers which is referring to the name.

Example:
Name: Peter (dropdown list)
Address: London Street 11 automatic
Tel.nr: 12345678910 automatic

What I did so far:
• Imported the database to sheet “Clients”. I have used a function Data > Import external data > New database query. Then I have imported table from database with clients.
• Then I have used Insert > Names > Define function to add the selected table from the “Clients” sheet. Typed name and refer to: =Filename.xls!Clients
• In the main sheet where drop down list should be made I have used a Data > Validation > in Allow box I chose a “list” > in Source box I entered equal sign and list name. When I press ok button I gives me a error “the sours have error at the moment, do you want to proceed” (it should be something like that, I have Dutch version of Excel)
Unfortunately I don’t get a drop down list.

I have also tried a vertical search option but it doesn’t work either.

Does anyone have ideas where do I make mistake? How can I make it work?

I hope you have a solution for me.


Thanks in advance,

Greetings,

Peter

Originally Posted by corinereyes

Hi,

Sample attached. Using the function VLookup to automatically display the details and using Named Ranges. I made a sample .mdb and importing data to excel sheet 2.

Corine

Thanks allot for your replay Corine!!

It’s exactly what I was looking for. You have saved me hours of searching on the internet for the solutions!

Thanks a lot again!!