Second, to create cell drop downs on Sheet1 (for example...and lets say your Name is in Column A on Sheet2), click Data then Validation on the menu bar. In the Data Validation popup ("DV"), change the "Allow" criteria to "List" then in the source ref box put in =Sheet2!$A$1:$A$10 (this assumes you have ten rows of data).
Now to look up the address based on the value selected from the dropdown you'll use vlookup. Let's assume the dropdown was in cell A1 on Sheet1 and you want to populate the address in Sheet1 in cell B1. So to achieve this, in Cell B1 of Sheet1 you'll put the following function: =VLOOKUP(A1,Sheet2!$A$1:$B$10,2,False)
The above function simply means..Lookup cell A1 name value, in Sheet2 column A, and return the 2nd column of the range (column B in this case), and the last part of the function we'll put "False" because we want an exact name match.
Ok, and thanks for you help again, but I am still having a little problem.
On Sheet2, A13:A17 has Customer Names, B13:B17 has Address 1, and C13:C17 has Address 2 (The contents are dummy informations that I am using for Test). Also, I used Define Name for cells A13:A17 as Customer_Name.
In Sheet1, I have A11 as the Drop-Box using Allow: List, Ignore Blank, In-cell dropdown, and Source: =Customer_Name. The Drop Down List works fine, showing customer names only.
Here is the problem. For cell A12, I would like Address 1 returned based on the Customer Name selected. This is the string entered: =VLOOKUP(A13,Sheet2!$A$13:$C$17,2,FALSE). I am getting a #N/A return.
Two things to note, not sure if they have any effect.
1. I am using Excel 2007.
2. On Sheet2, A12 reads "Customer Name," B12 reads "Address 1," and C12 reads "Address 2.)
Thanks for any help again.