create drop down box
 

I want to create a drop down box on say Sheet 1 in Cell B2 that will give me
the option of choosing four different people. Then when I choose one of the
four people, columns B6:B12 and B15 will automatically be filled in with the
people associated with one of the four people I selected. I figured this
would be possible with just creating a list of four people on sheet 2 and
their associated people under them. So you would have four columns on sheet
2 to link with the drop down box on sheet 1.
What is the easiest way to do this?

create drop down box
 

Hi David,

This is quit hard to explain. Send me an email, I will respond with a
example-file.

Regards, Gert-Jan

"David Altstatt" <David schreef in
bericht ...
I want to create a drop down box on say Sheet 1 in Cell B2 that will give
me
the option of choosing four different people. Then when I choose one of
the
four people, columns B6:B12 and B15 will automatically be filled in with
the
people associated with one of the four people I selected. I figured this
would be possible with just creating a list of four people on sheet 2 and
their associated people under them. So you would have four columns on
sheet
2 to link with the drop down box on sheet 1.
What is the easiest way to do this?

create drop down box
 

Check out:
http://www.contextures.com/xlDataVal02.html


"Gert-Jan" wrote in message
:

Hi David,

This is quit hard to explain. Send me an email, I will respond with a
example-file.

Regards, Gert-Jan

"David Altstatt" <David schreef in
bericht ...
I want to create a drop down box on say Sheet 1 in Cell B2 that will give
me
the option of choosing four different people. Then when I choose one of
the
four people, columns B6:B12 and B15 will automatically be filled in with
the
people associated with one of the four people I selected. I figured this
would be possible with just creating a list of four people on sheet 2 and
their associated people under them. So you would have four columns on
sheet
2 to link with the drop down box on sheet 1.
What is the easiest way to do this?

create drop down box
 

Try this example:

Sheet2:
A1:B13 contains this list
Mgr Staff
Manager_1 Worker_01
Manager_1 Worker_02
Manager_1 Worker_03
Manager_2 Worker_04
Manager_2 Worker_05
Manager_2 Worker_06
Manager_3 Worker_07
Manager_3 Worker_08
Manager_3 Worker_09
Manager_4 Worker_10
Manager_4 Worker_11
Manager_4 Worker_12

D1:D5 contains this list
MgrList
Manager_1
Manager_2
Manager_3
Manager_4

Set the name of D2:D5 to MgrList

Sheet1:
B2 is the DV referencing the range: MgrList

A6:A12 contains 1 through 7

Put this ARRAY FORMULA in
B6:
=IF(COUNTIF(Sheet2!$A$1:$A$13,Sheet1!$B$2)=Sheet1 !A6,INDEX(Sheet2!$B$1:$B$13,SMALL(IF(Sheet2!$A1:$A 10=$B$2,ROW(Sheet2!$A1:$A10)),$A6)),"")

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B6 and paste into B7 through B12

Now...when you select a manager from B2, that manager's staff lists in B6:B12

NOTE: I couldn't guess what you'd need in B15.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"David Altstatt" wrote:

I want to create a drop down box on say Sheet 1 in Cell B2 that will give me
the option of choosing four different people. Then when I choose one of the
four people, columns B6:B12 and B15 will automatically be filled in with the
people associated with one of the four people I selected. I figured this
would be possible with just creating a list of four people on sheet 2 and
their associated people under them. So you would have four columns on sheet
2 to link with the drop down box on sheet 1.
What is the easiest way to do this?