Autres Formule excel version 2007

=SOMMEPROD(NB.SI(E12:J12;V$11:AA$11))=NB(V$11:AA$11)

=SI(LIGNES(V$12:V12)>NB.SI($T$12:$T$2000;VRAI);"";INDEX(E$12:E$2000;PETITE.VALEUR(SI($T$12:$T$2000;LIGNE($T$12:$T$2000)-11);LIGNES(V$12:V12))))

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Recherche As Range, P As Range, Dates As Range, Complement As Range, c As Range, Q As Range, R As Range
Set Recherche = [T11:X11]
Set P = [E:I]
Set Dates = [D:D]
Set Complement = [J:J]
If Intersect(Target, Recherche) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
Recherche.Offset(1).Resize(Rows.Count - Recherche.Row, Recherche.Columns.Count + 1).Delete xlUp 'RAZ
For Each c In Recherche
    If c <> "" Then
        P.Replace c, "#N/A", xlWhole
        Set Q = Nothing
        Set Q = P.SpecialCells(xlCellTypeConstants, 16)
        If Q Is Nothing Then Exit Sub
        Q = c
        Set Q = Intersect(Q.EntireRow, P)
        If R Is Nothing Then Set R = Q Else Set R = Intersect(Q, R)
        If R Is Nothing Then Exit Sub
    End If
Next
'---résultat---
R.Copy Recherche(2, 1)
Intersect(R.EntireRow, Complement).Copy Recherche(2, Recherche.Columns.Count + 1)
Intersect(R.EntireRow, Dates).Copy Recherche(2, Recherche.Columns.Count + 2)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Recherche As Range, NC As Range, P As Range, Dates As Range, Complement As Range, c As Range, R As Range, Q As Range
Set Recherche = [T11:X11]
Set NC = [Y11] 'recherche du numéro complémentaire
Set P = [E:I]
Set Dates = [D:D]
Set Complement = [J:J]
If Intersect(Target, Union(Recherche, NC)) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
Recherche.Offset(1).Resize(Rows.Count - Recherche.Row, Recherche.Columns.Count + 2).Delete xlUp 'RAZ
If NC <> "" Then
    Complement.Replace NC, "#N/A", xlWhole
    Set R = Complement.SpecialCells(xlCellTypeConstants, 16)
    If R Is Nothing Then Exit Sub
    R = NC
    Set R = Intersect(R.EntireRow, P)
End If
For Each c In Recherche
    If c <> "" Then
        P.Replace c, "#N/A", xlWhole
        Set Q = Nothing
        Set Q = P.SpecialCells(xlCellTypeConstants, 16)
        If Q Is Nothing Then Exit Sub
        Q = c
        Set Q = Intersect(Q.EntireRow, P)
        If R Is Nothing Then Set R = Q Else Set R = Intersect(Q, R)
    End If
Next
'---résultat---
R.Copy Recherche(2, 1)
Intersect(R.EntireRow, Complement).Copy NC(2)
Intersect(R.EntireRow, Dates).Copy NC(2, 2)
End Sub