dynamically populate dropdown lists

[JasperD]

Hi guys and girls,

I've got a bit of a challenge :

I have a sheet, on which in column A3 down there are part numbers - these are filled in by the user and have the format of 7 digits, starting with a 1. (For example 1234567)
These partnumbers COULD be on one of five other sheets, in column A of those sheets (sheet names are "1", "2", "3", "4" and "5")

Basically I want, after the user inputs the part number in column A, a dropdown menu (data validation is fine) to appear in the same row in column E, with the following results :

a) If the part number is found on for example sheet "3" and "4" -->
Cell in column E shows "pick your sheet" and has options "3" and "4" as data validation dropdown

b) If the part number is only found on sheet "x" (for example "2") -->
Cell in column E shows the sheet name "2" in the cell - no other options available

c) If the part number is found on none of the sheets -->
Cell in column E shows "none" - no other options available.

The check of this can be triggered on worksheet_change or even worksheet_selection change.

Is this possible and if so, how should I go about it.

I much prefer using VBA over formulas.

Thanks for any help!

[JasperD]

hi hi - anyone? :|

[MickG]

Try this:-
The way you have named you sheets can lead to some confusion so I have changed a few thing:-
Your Basic Sheet is Called:- Data
The Five other sheets are named :- Sht1,Sht2,Sht3,Sht4 and Sht5.
The validation cell Headers have changed slightly, you will see. NB:- You require to keep the "_" in the header string
Everything else remains the same.
Nb:- When you Insert a number in columns "A" of shet "Data" and the Validation list arrives in column "E" and you select a sheet name, then the code will select that sheet, although you don't explicitly state that requirement.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range
Dim n As Integer
Dim Dic As Object
Dim Str As String
Str = vbNullString


If Not Target = vbNullString Then
    If Target.Column = 1 Then
        Set Dic = CreateObject("scripting.dictionary")
            Dic.CompareMode = vbTextCompare
For n = 1 To 5
    With Sheets("Sht" & n)
        Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
            For Each Dn In Rng
                If Not Dic.exists(Dn.Value) Then
                    Dic.Add Dn.Value, Dn.Parent.Name
                Else
                    Dic.Item(Dn.Value) = Dic.Item(Dn.Value) & "," & Dn.Parent.Name
                End If
            Next Dn
    End With
Next n

Str = Dic.Item(Target.Value)
Select Case UBound(Split(Str, ","))
    Case -1: Str = "None_Available"
    Case 0: Str = "Only_one_Option" & "," & Str
    Case Is > 0: Str = "Pick_Your_Sheet" & "," & Str
End Select

With Target.Offset(, 4)
    .Validation.Delete
    .Validation.Add Type:=xlValidateList, Formula1:=Str
    .Value = Split(Str, ",")(0)
End With

ElseIf Target.Column = 5 And Target.Count = 1 Then
   If InStr(Target, "_") = 0 Then
   Sheets(Target.Value).Select
    End If
End If
End If
End Sub

Regards Mick

[JasperD]

Hi Mick,

haven't tried it yet, but from reading the code, it looks like it will work splendid - I'll keep you posted!