creating a drop down list for names and addresses

**gracious** · 04-02-2008, 02:46 PM

I have a form where I have to have the following info on the top left corner of the form for our clients:

TO:Name of client
Address, city, zip...
Phone number
Fax Number

I need to find a way to create a drop down list where I have my contacts information so that if I pick a name, it will show up on my form but also have the address, phone....in the format as above so that I don't have to keep inputting the information manually as there are over 20 names, any ideas?

**Dunc3142** · 04-02-2008, 04:06 PM

If this is what you want let me know and I will send instructions on how I did it.

**gracious** · 04-02-2008, 06:32 PM

wow, yes that is what I am trying to achieve!!! Please send me the instructions, my hero, ty ty ty!! LOL

So I can change font size....to make it fit in the spreadsheet? Also, can I have the fax number on its own line? And I know I am being picky, anyway to have a comma between city and state. I am doing this for one of my managers who doesn't have a clue on how to use excel and I want to be able to plug this into his existing sheet without making him redo it.

Out of curiosity, why isn't there a sheet 1?

Thank you so much for all of your help!!!!!

**gracious** · 04-04-2008, 01:39 PM

I have been waiting for Dunc3142 to reply back with the instructions on how he made his sheet. I really need this and am hoping someone else can tell me how to make vlookup.

Here is what I have done so far, my working sheet is on sheet 1 and on sheet 2 (which I renamed as Addresses) I added all of our contractors names, addresses.....and did a name range on the cells with the names and named it "Names" and on sheet 1, I was able to do the validate, list and put "=Names" in the source and my drop down list now shows all of the contractors names.

What I cannot figure out is the vlookup formula so that the street, city are automatically added on the next line and also the line for the phone and fax.

Could someone please let me know how to do that.

Thank you so much!!

**darkyam** · 04-04-2008, 01:45 PM

VLOOKUP takes 4 arguments: a value to be looked up, an array to look it up in, the column to return from that array, and TRUE or FALSE (denoting either closest match without going over or exact match, can also be represented by 1 and 0).
To take the Sara Johns example his workbook opens with, A2 starts by looking up Sara Johns on Addresses, column A, then returns the value in the second column once it finds a match.

**ChemistB** · 04-04-2008, 02:27 PM

To answer some of your other questions, let's look at the formula in A2.

=VLOOKUP(A1,Addresses!A2:G50,2,FALSE)&"   "&VLOOKUP(A1,Addresses!A2:G50,3,FALSE)&"  "&VLOOKUP(A1,Addresses!A2:G50,4,FALSE)&"  "&VLOOKUP(A1,Addresses!A2:G50,5,FALSE)

Here Dunc is using the concatenate feature to string together a series of results from VLOOKUP (using &) with spaces inbetween them (&" "&). In order to put in a comma, instead of a space between the VLOOKUPs returning the Town and state, use &", "&

To put the Fax # on it's own line (in it's own cell), Break up the formula in cell A3 into two cells.

="Phone "&VLOOKUP(A1,Addresses!A2:G50,6,FALSE)&"  FAX"&VLOOKUP(A1,Addresses!A2:G50,7,FALSE)

Cut after the first "FALSE)" and put the second part into cell A4 beginning with an equals sign.

There is no Sheet1 because Dunc renamed it Addresses and moved it to after Sheet2.

ChemistB