Excel data validation - populated drop-down list

Question

I have this main table on Sheet1:
Job Language Name
1 English John
2 French Frank
3 Spanish Chuan
4 English James
5 English John

I managed to setup Data Validation on Language column, so there is a drop-down list with Languages. However, I would like to have a drop-down menu in column Name with anyone who speaks selected language from Sheet2 table.

Sheet2 looks like this - there are Languages and Names:

Language Name
English John
English James
French Frank
Spanish Chuan
Spanish Alejandro

As you see the point is that there are multiple people for each language.

Is there any way to show only the people for selected language in the drop-down menu?

Like this: select language -> display only people who speaks that language in drop-down

Thank you for any help!

Answer 1

In VBA code editor (ALT+F11) add worksheet function conected to event CHANGE

Below code of VBA procedure. When you change language, then procedure starts. It changes all data validation lists using list of persons in sheet2.

Please give feetback if procedure works on your machine properly.

Option Base 1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrTemp() As Variant
Dim intNumberOfRowsSheet1 As Integer, intNumberOfRowsSheet2 As Integer
Dim i As Integer, j As Integer
Dim strCurrentLanguage As String
Dim wksSheet1 As Worksheet, wksSheet2 As Worksheet
Dim intNumberOfPersons As Integer

 Set wksSheet1 = Worksheets("sheet1")
 Set wksSheet2 = Worksheets("sheet2")

intNumberOfRowsSheet1 = wksSheet1.Range("a1").CurrentRegion.Rows.Count  'number of rows in sheet1
intNumberOfRowsSheet2 = wksSheet2.Range("a1").CurrentRegion.Rows.Count 'number of rows in sheet2

 If Target.Column = 2 Then     'execute when value in column 2 (language) changes

   For i = 2 To intNumberOfRowsSheet1
      strCurrentLanguage = Cells(i, 2).Value  
      intNumberOfPersons = 0
      ReDim arrTemp(1)
        For j = 2 To intNumberOfRowsSheet2
          If wksSheet2.Cells(j, 1) = strCurrentLanguage Then
            intNumberOfPersons = intNumberOfPersons + 1
            ReDim Preserve arrTemp(intNumberOfPersons)
            arrTemp(intNumberOfPersons) = wksSheet2.Cells(j, 2)
          End If
        Next j
      If intNumberOfPersons = 0 Then arrTemp(1) = "no persons speaks " & wksSheet1.Cells(i, 2)
      With wksSheet1.Cells(i, 3).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(arrTemp, ",")
      End With
   Next i
 End If
End Sub