Excel/Corresponding Drop-down Lists
QUESTION: Good Afternoon Andrea!
I have a rather detailed Excel spreadsheet for which I am trying to sync two drop-down lists. I am currently using Microsoft Office Excel 2007. My first drop-down list is located in J7 and contains a list of 71 contact names. My second drop-down list is located in J9 and contains the telephone numbers for each of the 71 listed contacts. The contact information is listed in Z1:Z71 and the telephone list is located in AF1:AF71. I would like the telephone number to automatically populate when the corresponding contact is selected. I have searched the internet for three days and can't seem to find anything that specifically addresses my request. Can you help me with this?
I am wondering if you need a drop-down list for the phone numbers, if they are supposed to populate automatically with the contact name. If you use a VLookup for the phone numbers, then when a cell is populated with a contact name the VLookup would automatically use that information to find the correct phone number and populate it's cell.
Is there a reason you need the phone lookup?
Please get back to me - Andrea
---------- FOLLOW-UP ----------
QUESTION: Thanks for responding so quickly Andrea! The reason I created a drop-down list for the telephone numbers is because I thought it would be easier to sync the two fields together based on information/suggestions I read on the Internet. However, I am completely open to any and all ideas you may have to help me accomplish the task at hand, especially if the solution makes the whole process easier. The Excel spreadsheet is used to request security badges from my 105 badge contacts and the telephone numbers make it easier for me to contact the liaison if I have a question about a request. Again, thank you and I appreciate all your help!
ANSWER: Do you need help setting up the VLookup, or are you familiar with that?
Make sure you make a backup of your Workbook so that you don't lose anything in case you don't like how it works! :)
---------- FOLLOW-UP ----------
QUESTION: I am not familiar with VLookup and need help with that function. Thanks again for everything!
This is going to be long.
VLookup isn't difficult, not if you've ever gone shopping!
Have you ever gone to a store that was having a tag sale - the color of the tag tells you the percentage off the item? You find an item you want to buy, look at the tag, and then look at one of the tables they've posted. Find your color, follow the dotted line to the right, and you find your percentage discount. Well, that's how VLookup works.
First thing you need is the table. The table needs to contain the item you're looking for [color] and any information about that item [discount]. In your case, your table should have the name of the contact and their phone number. You already have this information in your spreadsheet, you just need to put them next to each other. The item you are looking for should always be first, and MUST be in alphabetical order. If it isn't, VLookup will bring back the wrong data!
To make it easy to use the formula, select all of the information and name the selection by clicking up in the box at the upper left part of your screen (it probably has a cell name in it, like "A1"). Type in the word "table" or "data" or anything you want to call it, so long as there are no spaces. Hit enter, and you've Named your Range. Otherwise, you can use the array - "$B$2:$D$30" for example.
Now go to the first cell where you want to fill in the phone number when a contact is picked from the drop down. Here you are going to enter the following formula:
The Lookup_value is the item you're looking at, the colored tag, the client name.
The Table_array is the chart with the discounts; the table you created and named.
Col_index_num is the only confusing part. You're not interested in columns like A B C... you'll tell the column by number. In your table, client name is column 1. The next column is phone number, which is column 2. And so on, if you have other information.
Range_lookup is optional, and you don't need it here.
So, for example, your formula will be =VLOOKUP(C3,table,2) This means take whatever name was chosen from the drop-down in cell C3, find it in my table, then bring back whatever you find next to it in column 2 (phone number).
Does this make sense? - Andrea