Auto-Filling Fields

**MFO_Jester** · 04-12-2010, 07:57 PM

I am trying to create sort of a form in Excel 2003 and what I want to do is have a box where you can select (like a drop down menu) or type in a job name and then have it fill out a bunch of other cells automatically.

The data would be on a separate sheet in the same document and have fields like job name, job number, address, contact information, and so on. I want to be able to type or select a job name and then have it pull the information from the second sheet and insert the corresponding information into separate cells.

Is this possible?

I'm not very good with the whole formula/macro thing in Excel so any help would be grateful.

**Palmetto** · 04-12-2010, 09:51 PM

You've posted your question in the VBA section of the Forum, however, you don't need VBA code to accomplish what you have described. That can be done, with a properly structured worksheet and look up table, using a look up function like VLOOKUP.

See the attached basic example. The drop down in cell A2 is a Data Validation list using a named range as the list source.

Sheet2 holds the lists.

**MFO_Jester** · 04-12-2010, 10:00 PM

Sorry, I didn't realize.

Thanks for the help.

**MFO_Jester** · 04-13-2010, 02:20 PM

I figured out how to do the drop down list but I can't get the VLOOKUP command to work. I'm attaching a sample file of what I am looking for.

The problem I'm having is that the list on my form isnt set up the same as the second sheet. the fields are in different areas and they aren't on the same line.

Please look over the file and see if you can help me.

Thanks,

Jester

**Palmetto** · 04-13-2010, 08:47 PM

The problem I'm having is that the list on my form isnt set up the same as the second sheet. the fields are in different areas and they aren't on the same line.

What's important is the structure of the look up table, which can be made to work with virtually any layout of the form (limitations apply depending on the look up method used).

If you will always be looking up values based on the Job Name and job name in the look up table is always the first column (left-to-right), then the VLOOKUP formula will suffice, otherwise you would need a combination of INDEX/MATCH functions.

In this look up formula the range references are hard-coded. If you expect the look up table to increase or shrink over time, then I suggest you create a dynamic named range for the table and use the named range in place of the hard-coded reference.

Formula in cell M5 (Job #): =VLOOKUP(N4,Sheet2!B2:E4,2,0)

See attached.