You are here:

Excel/Loading Data by Selecting from a List

Advertisement


Question
I used the Data Validation "List" function and assign it to a field in a worksheet.  I want to create programming for each selection in the list so that when one is selected, several different fields are populated with associated data.  For example, if the list contains four different customers named Customer 1, Customer 2, etc., and I select Customer 2 then the Street Address for that Customer loads into one, different cell, the city, state, zip loads into yet another cell, the Customer Code Number loads into yet another.  Is this possible and how can it be done?  Thanks.

Answer
This means that you have a second sheet in your workbook where you have a table setup listing all you customer information, correct?

The way I do this very thing is to use the first column of the data sheet as the source of the Data Validation LIST.  This way we are sure the spelling will always be an exact match.

Then in the rest of the form fields, you will use a VLOOKUP formula to get the related data from the same data row as the name you selected in the first form field DV drop down.

VLOOKUP example, assuming the first field is cell A5, and this formula needs to pull back a value from column C of the data sheet:

=VLOOKUP($A$5, Data!$A:$Z, 3, 0)    

Notice way VLOOKUP works?  

First Paramater - $A$5 - the value to lookup in the data table

Second parameter - Data!$A:$Z - a table of the first 26 columns on the Data sheet, the FIRST column in this range of columns must be the column the first parameter has to match

Third parameter - 3 - the column in the table you want to bring back a value from, in this case the 3 means it will come from column 3, the third column in the range of columna A:Z.  If the range of columns we had noted was M:Z, then the third column would be column P.

Fourth parameter - 0 - this means the first two parameters must match exactly.  Read up in the F1 Help files on Vlookup, there are times you may use VLOOKUP to do a "fuzzy match" and still work, usually in regards to looking up tiered pricing or such in a table.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.