Drop Down List Question!

[Bob Vance]

How do I go about having a drop down list in a invoice so as it will link up
with a name and address on another sheet.
Sheet (Address) has name and address in A1, A2, A3, A4, A5. Then in Sheet
Invoice I want to have a drop down list then select and the next 4 cells
fall below to complete a invoice address!
--
Thanks in advance for your help....Bob Vance
..
..
..
..

 

[SMILE]

Hello

Select the cells of your address (A1:A50) in Sheet Address Then |
Insert| Name | Define
give a name - for eg: "add"

Now in your Invoice Sheet, go to the cell where you want the address to
be printed
Data | Validation | Allow | selet list
After selecting list, in Source type =add

Hth

Toms

 

[Debra Dalgleish]

Use a data validation list to select the name in cell A1. Then, use
VLookup formulas to return the address information in the adjacent
cells. There's a sample order form that uses this technique here:

http://www.contextures.com/excelfiles.html#DataVal

Look for 'Order Form' and download 'DataValOrder.xls'

 

[Bob Vance]

What am I doing wrong?
=VLOOKUP($DN$150,ADDRESS,2,0)
I have a list of names in another sheet in the same workbook , under name
define "Address" and trying to get there address in the cells below with
this formula but I am doing something wrong, I am getting #Ref!

--
Thanks in advance for your help....Bob Vance
..
..
..
..

 

[Debra Dalgleish]

Does the Address range have two or more columns?

What am I doing wrong?
=VLOOKUP($DN$150,ADDRESS,2,0)
I have a list of names in another sheet in the same workbook , under name
define "Address" and trying to get there address in the cells below with
this formula but I am doing something wrong, I am getting #Ref!

--
Thanks in advance for your help....Bob Vance
.
.
.
.


link up

 

[Dave Peterson]

Does your range named Address have more than one column?

Your =vlookup() wants to return the value from the second column.

 

[Bob Vance]

A Column is the names under "Address" name define, Columns BCDE has there
street address

--
Thanks in advance for your help....Bob Vance
..
..
..
..

 

[Bob Vance]

aahhhhhhhhh........got it, Name Define all 5 Columns GREAT

--
Thanks in advance for your help....Bob Vance
..
..
..
..

A Column is the names under "Address" name define, Columns BCDE has there
street address

--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Bob Vance]

Drop down list now has the full address in it, is that correct?

--
Thanks in advance for your help....Bob Vance
..
..
..
..

aahhhhhhhhh........got it, Name Define all 5 Columns GREAT

--
Thanks in advance for your help....Bob Vance
.
.
.
.

A Column is the names under "Address" name define, Columns BCDE has there
street address

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Then

 

[Debra Dalgleish]

You need one named range for the list of names (Column A)

You need another named range that contains the names and the address
(columns A:E)

For example,

Name Street City Prov PCode
Fred 123 Main Ottawa ON K1A 1A1
Mary 345 Pine Calgary AB T2P 3L3

Then, to create an invoice
A B C
1 Name Street city
2 Mary =VLOOKUP(A2,Address,2,0) =VLOOKUP(A2,Address,3,0)

 

[Bob Vance]

BRILLIANT Debra thanx for the wonderful help!

--
Thanks in advance for your help....Bob Vance
..
..
..
..

 

[Bob Vance]

Debra if I click on a blank cell my 3 other cells give a annoying #N/A if I
don't want an address in a invoice, I suppose there isn't anything to get
rid of it?

--
Thanks in advance for your help....Bob Vance
..
..
..
..

 

[Debra Dalgleish]

You can check for an error, with an IF function:

=IF(ISNA(VLOOKUP(A2,Address,2,0)),"",VLOOKUP(A2,Address,2,0))

 

[Bob Vance]

Thanks Debra that did the trick

--
Thanks in advance for your help....Bob Vance
..
..
..
..