dynamically populate dropdown lists

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