Using a dropdown list to open another list

[rholdren]

Is there a way to use a dropdown list to select a second list. I've been searching but can't
find anything quite like this, I know that sounds a little weird but here is what I am trying
to do:
I have Five lists. My first list is a list of
supervisors and then 4 seperate lists. Each of these lists belong to a particular
supervisor (their team members). What I would like is if I choose Supervisor#1 the list
containing their team would populate in the cells A3 down to A50. If I choose Supervisor#2
their team would then replace the previous team in that Range. and so on. I'm okay
with build lists and dropdown boxes I just can't figure out how to pull up a list and have it

populate a range based on the selection in the dropdown box.
Any help would be greatly appreciated.

Thanks

 

[alansidman]

There are lots of tutorials on this. Google "Excel, Dependent Combo Boxes" and there will be several on ways to do this.

 

[rholdren]

I was really hoping I could do this without building combo boxes. I just want to be able to use a list to control the contents of a range.

 

[MickG]

Try this:-
In sheet1 "A1" have you dropdown with Supervisor 1, Supervisor 2 etc ( Make sure there is a space between supervisor and the number.
In Sheet 2 columns 1 to 4 have your lists.

Copy the code below then In Sheet1, right click the sheet Tab, select "View Code", VBwindow appears, Paste the code into the Vbwindow, close VBwindow
Now when you select an item from the dropdown the related list should appear starting "A3".

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, col [COLOR=navy]As[/COLOR] Variant
    [COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
        col = Split(Target.Value, " ")(1)
        [COLOR=navy]With[/COLOR] Sheets("Sheet2")
            [COLOR=navy]Set[/COLOR] Rng = .Range(.Cells(1, Val(col)), .Cells(Rows.Count, Val(col)).End(xlUp))
        [COLOR=navy]End[/COLOR] With
        Range("A:A").ClearContents
        Range("A3").Resize(Rng.Count) = Rng.Value
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[alansidman]

Two sheets; On Sheet1, B1 is a combo box for the Five names
the list of employees will be placed in Column D1:D50

On the Sheet named Lists, first list of Employees in column A1:A50, second in B1:B50 etc.

I have used the names listed in the case select scenario. Change them as needed.

Here is VBA code to do as you wish

Option Explicit

Sub EEs()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1"): Set s2 = Sheets("Lists")
    s1.Range("D1:D50").ClearContents
    Dim Super As String
    Super = s1.Range("B1").Value
    Select Case Super
    Case "Jack"
        s2.Range("A1:A50").Copy s1.Range("D1")
    Case "Jim"
        s2.Range("B1:B50").Copy s1.Range("D1")
    Case "Jill"
        s2.Range("C1:C50").Copy s1.Range("D1")
    Case "Jane"
        s2.Range("D1:D50").Copy s1.Range("D1")
    Case "Joe"
        s2.Range("E1:E50").Copy s1.Range("D1")
    End Select
    Application.CutCopyMode = False
End Sub