creating a drop down list for names and addresses

[gracious]

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]

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

[gracious]

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]

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]

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]

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

[gracious]

Thank you so much for the replies. I appreciate the explanations as well.

Ok, so do I just highlight A2 and at the top input the code in the formula bar or do I have to do the vba F11? If my contractor lists has 20 names, do I just copy and paste the code but keep changing the number?

One more thing, when it shows A2:G50, what does G50 refer to?

Again, thank you so much for the help and info!!!!

[darkyam]

Do you mean you want the lookup to occur 20 times on the page? If so, change the array reference to Addresses!$A$2:$G$50. As long as the lookup is just above the cells where the formulas are (i.e., each address appears in the same group format as it is now), you won't have to manually change anything else. G50 refers to the last cell in the lookup array. In other words, these formulas will try to match the name in A1 with the values in A2:A50 on the Addresses tab and can return any value from that array out to column G.

[gracious]

Yes I think that is correct, when I look at his sheet, his vlookup strings, he has 2,FALSE and then 3,FALSE....I assumed because of the number of names.

I tried putting in the code but all I got was #N/A
Obviously I am not doing something correctly.

[darkyam]

Could you please post the formula you used or the workbook?

[gracious]

oh, I see, the 2,3,4,....refers to the columns in the addresses sheet,not to how many names there are! and the back to back is just adding a new value to the same line so that they appear next to each other, oofta!! Light bulb just came on.

[gracious]

How do I upload the worksheet? I suppose I should remove the company name, bla bla first?

[ChemistB]

You'll need to zip/compress it first. Most computers have the software to do that. Find the file on drive from Windows Explore and right click on it. One option should be to WINZIP>Add to WINZIP file of the same name. Then when posting a reply scroll down to "Manage Attachments" button.

ChemistB

[gracious]

Ok, I got it to work. If you guys hadn't explained how the code was broken down I don't think I would have understood. I was using his code but forgot to change the value of A1 to on my sheet what is A8.
It now works just beautifully.

There is one small, eensy, teensy thing, you see on his sheet where the phone and fax numbers are? There is a nice space between the word Phone and the actual number but there isn't a space between the word Fax and the number. Anyway to fix that? If not, it is no biggie I guess, considering what I have accomplished with all of your help!

LOL, ok never mind on the spacing by the word Fax, I figured that out too!!

Anyway, thank you Dunc for getting the ball rolling and thank you Darkyam and Chem for helping me getting it completed!!!
You are awesome!!!!

[darkyam]

Add a space after the word FAX in the formula.

[gracious]

I am responding back to my original thread because I am having an issue with my newly created form with the dropdown.

I added 3 more names to my dropdown list via the address page and resorted the data for the names to be in alpha order, but when I go to look to see if the new names are in the drop down list, it looks like that it cuts off some of the names.

I don't know if in the forumula I am suppose to tell it a new range or if it has to do with the sheet the dropdown list is used on.

How do I get it to read more names on the list?

[ChemistB]

It sounds like your dropdown list (validation?) range is less than the actual length of your list. You'll need to double check that. If that's not the problem, it would help if you uploaded an example of your workbook.

ChemistB

[gracious]

Ok, here is the worksheet. As you can see the dropdown list only goes to the name of Trademark and doesn't show the last 3.

Any help is most appreciated!!

[ChemistB]

Hi
Your dropdown/validation list uses the defined name "names". It is defined as [code]=Addresses!$A$2:$A$21[/codes]
You need to extend that to include all your names (up to A24 at the moment).

1. Menu Insert>Names>Define
2. Click on "names" and in the "refers to box" update from $A$21 to $A$24. Hit "Add" then "OK"
3. Your list should show all your names now.

Any questions?

ChemistB

[gracious]

Wow so simple, that did the trick. I swear I looked all over the place and totally forgot that I had a 'named' range.

Thank you again chem!!!

[ChemistB]

You're welcome.