I have a Data List A1:E10 with the first column listing by "Unit number" (501
- 509). In the remaining 4 colums I have 4 different vehicle types each of
which have a list of corresponding vehicle numbers for a given unit number.
My requirement is to use this table array as a reference and somewhere else
in the workbook I have a title area where I want to select a Unit Number from
the list (I can select this from a validation list) which automatically
inserts the corresponding vehicle numbers against the 4 vehicle types. I
cannot find which is the correct approach to solve this problem although I
have just been and bought Excel 2003 for Dummies, I still cannot decide the
correct and appropriate method, any help would be appreciated.

Say you enter the unit number in A1.
Maybe using data|validation so that you can't make a typo!!!

Visit Debra Dalgleish's site for lots of info:
http://www.contextures.com/xlDataVal01.html

Then you can retrieve the first value (column B of the table) with something
like:

=if($a$1="","",vlookup($a$1,sheet2!a:e,2,false)

the 3rd column:
=if($a$1="","",vlookup($a$1,sheet2!a:e,3,false)
....
The 5th column:
=if($a$1="","",vlookup($a$1,sheet2!a:e,5,false)

It doesn't do any error checking, since you used data|validation in A1 and you
can't make a typo!!!

Debra Dalgleish has lots of notes on =vlookup() he
http://www.contextures.com/xlFunctions02.html
and
http://contextures.com/xlFunctions02.html#Trouble

Paul wrote:

I have a Data List A1:E10 with the first column listing by "Unit number" (501
- 509). In the remaining 4 colums I have 4 different vehicle types each of
which have a list of corresponding vehicle numbers for a given unit number.
My requirement is to use this table array as a reference and somewhere else
in the workbook I have a title area where I want to select a Unit Number from
the list (I can select this from a validation list) which automatically
inserts the corresponding vehicle numbers against the 4 vehicle types. I
cannot find which is the correct approach to solve this problem although I
have just been and bought Excel 2003 for Dummies, I still cannot decide the
correct and appropriate method, any help would be appreciated.

--

Dave Peterson

Many thanks Dave, I shall investigate further and let you know when I find
the answer I am looking for.

Regards,
Paul.

"Dave Peterson" wrote:

Say you enter the unit number in A1.
Maybe using data|validation so that you can't make a typo!!!

Visit Debra Dalgleish's site for lots of info:
http://www.contextures.com/xlDataVal01.html

Then you can retrieve the first value (column B of the table) with something
like:

=if($a$1="","",vlookup($a$1,sheet2!a:e,2,false)

the 3rd column:
=if($a$1="","",vlookup($a$1,sheet2!a:e,3,false)
....
The 5th column:
=if($a$1="","",vlookup($a$1,sheet2!a:e,5,false)

It doesn't do any error checking, since you used data|validation in A1 and you
can't make a typo!!!

Debra Dalgleish has lots of notes on =vlookup() he
http://www.contextures.com/xlFunctions02.html
and
http://contextures.com/xlFunctions02.html#Trouble

Paul wrote:

I have a Data List A1:E10 with the first column listing by "Unit number" (501
- 509). In the remaining 4 colums I have 4 different vehicle types each of
which have a list of corresponding vehicle numbers for a given unit number.
My requirement is to use this table array as a reference and somewhere else
in the workbook I have a title area where I want to select a Unit Number from
the list (I can select this from a validation list) which automatically
inserts the corresponding vehicle numbers against the 4 vehicle types. I
cannot find which is the correct approach to solve this problem although I
have just been and bought Excel 2003 for Dummies, I still cannot decide the
correct and appropriate method, any help would be appreciated.

--

Dave Peterson