[Excel] Liste déroulante filtré selon donnée

'=DECALER(Feuil1!$A   ICI doit être incrémenté en fonction de la ligne non ?  3,,,NB.SI(Feuil3!$C$5:$C$28,Feuil1!$B$3),1)

http://cjoint.com/?0AnqgUIssUu

Private Sub Worksheet_Change(ByVal target As Range)
If Not (Intersect(target, [Client_Validation]) Is Nothing) Then
    Set vtarget_zone = target
    Set vNom_Assoc = [Noms_Assoc]
    Set vBiens_Assoc = [Biens_Assoc]
    Set vBiens_Valid = [Biens_Validation]
    Set vClient_Valid = [Client_Validation]
    Application.EnableEvents = False
    
    For Each vtarget In vtarget_zone.Cells        'pour  le cas où target contient plusieurs cellules
        If IsEmpty(vtarget) Or Not (Nom_valide(vtarget)) Then    'gestion de l'effacement du nom du client ou de la saisie d'un nom non-valide
            Set selection_bak = Selection
            vBiens_Valid(vtarget.Row - vClient_Valid.Row + 1).Select
            Selection.ClearContents
            With Selection.Validation
                .Delete
            End With
            selection_bak.Select
        Else
            nblig = vBiens_Assoc.Rows.Count
            L_Valid = "": j = 1:
            ReDim I_Biens(nblig)
            
            For i = 1 To nblig
                If vNom_Assoc(i) = vtarget Then
                    I_Biens(j) = i: j = j + 1
                End If
            Next
            'tri_alpha des biens
            nb_biens = j - 1
            For i = 1 To nb_biens - 1
                For j = i + 1 To nb_biens
                    If vBiens_Assoc(I_Biens(j)) < vBiens_Assoc(I_Biens(i)) Then
                        vswap = I_Biens(j): I_Biens(j) = I_Biens(i): I_Biens(i) = vswap
                    End If
                Next
            Next
            'mise en forme de la liste de validation
            For i = 1 To nb_biens
                L_Valid = L_Valid & "," & vBiens_Assoc(I_Biens(i))
            Next
            Set selection_bak = Selection
            vBiens_Valid(vtarget.Row - vClient_Valid.Row + 1).Select
            Selection.ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:=L_Valid
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            selection_bak.Select
        End If
    Next
        Application.EnableEvents = True
End If
End Sub

Function Nom_valide(vnom)
Nom_valide = False
For Each vcell In [nom]
    If vnom = vcell Then Nom_valide = True: Exit Function
Next
End Function

Sub enable_events()
    Application.EnableEvents = True
End Sub