تفضل أخى ابراهيم 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [F9].Address Then
For Each cl In [C5:IV5]
If cl <> "" Then myarr = myarr & cl & ","
Next
With Range("F9").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=myarr
        
    End With
End If
End Sub
قائمه1 .rar