Validation Pull-Down Lists

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

Hi... I need help with pull-down lists.

I want to list-select data from a column in another
workbook. I figured that out with the validation
function, but I also want the cells to the right to
automatically reference the cells to the right of
whichever item was chosen from the list.

Example:
In the master workbook, A1 is Dept, B1 is Contact, C1 is
Address, D1 is Phone.
In the other workbook, I choose a Dept. name in A1. I
want its corresponding Contact, Address, and Phone to
automatically follow.

Help?
Thanks!
 
Select the 3 cells you want this info in, with the leftmost cell as active
array enter

=IF(A1="","",VLOOKUP(A1,'[Master Workbook.xls]Sheet1'!$A$1:$D$50,{2,3,4}))

(enter with ctrl + shift & enter)

note that for the validation list to work both workbooks have to be open
 
formula in the "other" workbook:
=VLOOKUP(A1,[MASTER.xls]Sheet1!A2:D2,1)


Keep in mind that the data in the "master.xls" workbook
must be sorted in ascending order. Also, instead of A2:D2
in the formula you could make it easier to read, work
with, etc.. by naming the range.
 
Peo - I'd like to learn more about the array enter. Do
you have a link with detail information? Will that also
tell me why the {2,3,4} was added to your suggested
formula, and what it does (cause nothing was generated
when I used it in a test). Thanks.

Curious, wanting to learn more,


Ozzie..
(e-mail address removed)

-----Original Message-----
Select the 3 cells you want this info in, with the leftmost cell as active
array enter

=IF(A1="","",VLOOKUP(A1,'[Master Workbook.xls]Sheet1'! $A$1:$D$50,{2,3,4}))

(enter with ctrl + shift & enter)

note that for the validation list to work both workbooks have to be open

--

Regards,

Peo Sjoblom

Hi... I need help with pull-down lists.

I want to list-select data from a column in another
workbook. I figured that out with the validation
function, but I also want the cells to the right to
automatically reference the cells to the right of
whichever item was chosen from the list.

Example:
In the master workbook, A1 is Dept, B1 is Contact, C1 is
Address, D1 is Phone.
In the other workbook, I choose a Dept. name in A1. I
want its corresponding Contact, Address, and Phone to
automatically follow.

Help?
Thanks!
Click to expand...


.
Click to expand...
 
To array enter a vlookup formula you have to select (in this case) 3
cells, I assumed the OP would get the first part for the validation in A1,
given that it would be column 2, 3 and 4 (since the first gets select from
the validation)
Assume that A1 is the cell with validation, then B1 should return contact,
C1 address,
and D1 the ph #.. You can either put in 3 vlookup formulas for eacg cell
using
clookup(a1,range,2,0) vlookup(a1,range,3,0) and so on or you can do it in
one fell swoop.
First select B1, then hold down the mous button and select C1 and D1 as
well,
with default excel settings B1 will be white and C1 and D1 blue, that means
the active cell
is B1. Now click up in the formula bar tp place the cursor there, then paste
in the formula,
then to finish it enter at the same time ctrl +shift & enter. That will
result in returning the values
from the 2nd, 3rd and 4th adjacent cell to the lookup value.

http://www.emailoffice.com/excel/arrays-bobumlas.html

for further info

--

Regards,

Peo Sjoblom

Ozzie said:
Peo - I'd like to learn more about the array enter. Do
you have a link with detail information? Will that also
tell me why the {2,3,4} was added to your suggested
formula, and what it does (cause nothing was generated
when I used it in a test). Thanks.

Curious, wanting to learn more,


Ozzie..
(e-mail address removed)

-----Original Message-----
Select the 3 cells you want this info in, with the leftmost cell as active
array enter

=IF(A1="","",VLOOKUP(A1,'[Master Workbook.xls]Sheet1'! $A$1:$D$50,{2,3,4}))

(enter with ctrl + shift & enter)

note that for the validation list to work both workbooks have to be open

--

Regards,

Peo Sjoblom

Hi... I need help with pull-down lists.

I want to list-select data from a column in another
workbook. I figured that out with the validation
function, but I also want the cells to the right to
automatically reference the cells to the right of
whichever item was chosen from the list.

Example:
In the master workbook, A1 is Dept, B1 is Contact, C1 is
Address, D1 is Phone.
In the other workbook, I choose a Dept. name in A1. I
want its corresponding Contact, Address, and Phone to
automatically follow.

Help?
Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top