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?

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?

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?

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?