create drop down box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Altstatt - 09 Dec 2006 19:10 GMT

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?

Reply to this Message

Gert-Jan - 10 Dec 2006 16:41 GMT

Hi David,

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

Regards, Gert-Jan

>I want to create a drop down box on say Sheet 1 in Cell B2 that will give
>me
[quoted text clipped - 8 lines]
> 2 to link with the drop down box on sheet 1.
> What is the easiest way to do this?

Reply to this Message

JMay - 10 Dec 2006 17:38 GMT

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

> Hi David,
>
[quoted text clipped - 15 lines]
> > 2 to link with the drop down box on sheet 1.
> > What is the easiest way to do this?

Reply to this Message

Ron Coderre - 10 Dec 2006 18:29 GMT

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:$A10=$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

> 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
[quoted text clipped - 4 lines]
> 2 to link with the drop down box on sheet 1.
> What is the easiest way to do this?

Reply to this Message