Can i link drop down lists?

Evening happy campers,

In my workbook, Sheet 2, Range A1:A10 is a list of names - the range has been called =Names, to allow me to use a drop down list on sheet1.
These names are the area managers.
I also have a list of employee names in column D, for every cell in Column D, the adjacent cell in Column C has one of the managers names from A1:A10.

On Sheet1 i have added a drop down list in Cell A1, set from the range =Names.
What i would like is the following:

User selects a name from the drop down box in A1, then in C1 they have another drop down list that shows all employee names.

Have loaded a sample and tried to explain there also

Thanks in advance!!

Galvinpaddy

Hi, thanks for the speedy response!
Could i ask for you to talk me through it a little? just so i understand what i am changing, why and the effect it'll have?
If not i'll have a bash at it and see how it goes!

What you need to do is to create 10 further named ranges (one for each manager) and then the source of the second data validation will be:

=INDIRECT(manager's name shown in $A$1)

I've rearranged your data in Sheet2 so that it is easier to see, with each manager's name at the top of the column, and then used Name Manager to define the appropriate names as in row 1. One drawback with this is that you can't have spaces in the names of named ranges - Excel will change them to an underscore. Consequently, the source for the second DV in C1 is given by:

=INDIRECT(SUBSTITUTE($A$1," ","_"))

Hope this helps.

Pete

Hi,

just noticed that this wasn't the VBA part of the forum, my bad.

In any case, here's an alternative VBA solution you can use to be put into the Sheet1 module as an event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim a, i As Long

    If Target.Count > 1 Then Exit Sub
    If Application.Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

    If Len(Target.Value) Then
        a = Sheets("Sheet2").Range("C1").CurrentRegion
        With CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(a, 1)
                If Target.Value = a(i, 1) Then .Item(a(i, 2)) = Empty
            Next
                Range("C1").Validation.Delete
                Range("C1").Validation.Add 3, , , Join(.keys, ",")
                Range("C1").ClearContents
        End With
    Else
        Range("C1").ClearContents
    End If

End Sub

Best,
berlan

Pete_UK, you sir deserve a beer.

Thanks a lot fella

Originally Posted by FDibbins

Thanks for teh explanation, Pete

I was just about to post my workbook, having re-arranged all the data, and thought I'd better refresh the screen, and then discovered that you had got in before me, so I had to change what I had written.

Pete

Apologies - thanks also to both FDibbins & Berlan