# Excel - Drop Down Boxes



## Dreambringer (Jan 19, 2005)

I know this is possible in Access, but was wondering can it be done in excel.

Lets say I have a drop down box that has a list of names.

When the name is selected, it auto populates their Address/Phone Number.

Is this something that can be done using simple formulas or gonna have to be VBA?


----------



## OBP (Mar 8, 2005)

The initial bit is done with the Dropdown's VB, which you do not even have to know about.
ie you can select from a list stored in a column and put the result in to a fixed cell which is the one the dropdown is "bound to". After that you might be able to use lookup with the value in that cell, but it is much easier for me to use VB.


----------



## Dreambringer (Jan 19, 2005)

Yeah, I was thinking it was giong to be easier with VB, but I am still uber new to getting anything to work with VB..

Think you could get me started? point me in the right direction?


----------



## Anne Troy (Feb 14, 1999)

You don't need VBA at all. What you can use in Excel is Data Validation for the drop-down, and then a VLOOKUP to pull in the data. Here's some articles that may help you accomplish all of it.
Tutorial for such a document: 
http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm
Data Validation: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm
VLOOKUP: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm


----------



## Zack Barresse (Jul 25, 2004)

The trick in Excel is, to have the data pre-populated in another table somewhere else. From there, you can use the methods Dreamboat describes. Not sure how much that is like Access..


----------



## OBP (Mar 8, 2005)

Zack, typical of an Access application of a combo box.


----------



## Dreambringer (Jan 19, 2005)

Thanx Dreamboat!  and everyone else that replied while I was typing this.

I was actually going to take a bit a further, and actually have the first box

State > City > Name > Address > Phone Number

This is just an exercise that I am doing formyself, but once I was pointed in the right direction, I would have taken it apart to see how it work and added what I needed..


----------



## slurpee55 (Oct 20, 2004)

I find that VLOOKUP, while more powerful, it can be easier to just use LOOKUP, as I have in the attached file - just have two named ranges, Name and Address (as lovely Dreamboat mentioned) and on another sheet, the name is a data validation list and the addresses come via LOOKUP.


----------



## Zack Barresse (Jul 25, 2004)

Lookup is great for only returning one item, but where there are multiple values to be returned possibly, Vlookup is going to be the better suitor here.


----------



## slurpee55 (Oct 20, 2004)

Largely I would agree with you, but if it is a simple thing, as this request appears to be, VLOOKUP can be confusing (okay, at least to me) when LOOKUP will do the job. 
In this case, I would,say, select the name in column A and have a lookup for the address in column B and a lookup for the phone in column C. Very easy to do.


----------



## Zack Barresse (Jul 25, 2004)

I see your point. One of the largest reasons I would tend for a VLOOKUP instead is it's diversity in being able to copy over/down as needed, especially in a multi-column table.


----------



## slurpee55 (Oct 20, 2004)

I agree...I have often used VLOOKUP, but always seem to have to relearn it, too. Something about it just never seems to click with me. Duh.


----------



## bluebloods (Oct 2, 2004)

Hello  

Sorry to butt in 

This just doesn't seem to be working for me  I'm only doing a simple one can anyone help out?

Thanks if you can  :up:


----------



## bomb #21 (Jul 1, 2005)

Here ya go. 

If you need to add more places/prices:

1. Select the *Data* sheet

2. Press *CTRL+F3*

3. Select one of the names in the list (*Places* or *Prices*)

4. Click in *Refers to*, then click & drag on the sheet.


----------

