Créer une liste à partir / en fonction d'une autre

[mouftie]

Bonjour,
J'ai une table NomSecteur composé de 2 colonnes avec des noms définis : Noms et Sect.
Sur une autre feuille, je veux faire apparaitre par secteur, les données individuelles du secteur concerné.
J'ai donc créé en A3 une liste par Données/Validation =Sect
Je voudrais créer une liste en A4 qui ne fasse apparaitre que les noms des personnes appartenant au secteur choisit.
J'ai essayé de créer en A4 une liste par Données/Validation =INDEX(NomSect;EQUIV(A3;Sect;0);1), mais j'ai un message d'erreur de formule ?
Faut-il passer par une autre table ? si oui la quelle, par un code ? lequel.
Je vous joint un fichier test : http://cjoint.com/?CDekEPqkeGd

Je me rends compte qua ma liste de secteur n'est pas classé et qu'elle contient des doublons. Peut-on y remédier ?
Merci d'avance pour vos réponses / sugestions

 

[BOISGONTIER]

Re : Créer une liste à partir / en fonction d'une autre

Bonjour,

Voir PJ

Listes en cascade

JB

 

[Robert]

Re : Créer une liste à partir / en fonction d'une autre

Bonjour le fil, bonjour le forum,

Une proposition VBA, parce que j'y ai planché dessus, mais beaucoup moins efficace que celle de JB (d'ailleurs, le code que j'utilise est tiré de son site)
Le code commenté :

Private dl As Integer 'déclare la variable dl(Dernière Ligne)
Private pl As Range 'déclare la variable pl(PLage)
Private d As Object 'déclare la variable d(Dictionnaire)
Private cel As Range 'déclare la variable cel(CELlule)
Private tt() As Variant 'déclare la variable tt (Tableau Temporaire)

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'au changement de sélection dans l'onglet
If Target.Address <> "$A$3" Then Exit Sub 'si la cellule active est différente de A3, sort de la procédure
With Sheets("Tables") 'prend en compte l'onglet "Tables"
    dl = .Cells(Application.Rows.Count, 3).End(xlUp).Row 'définit la dernière ligne dl de la colonne 3 (=C)
    Set pl = .Range("C4:C" & dl) 'définit la plage pl
    Set d = CreateObject("Scripting.Dictionary") 'définit le dictionnaire d
    For Each cel In pl 'boucle sur toutes les cellules cel de la plage pl
        d(cel.Value) = "" 'alimente le dictionnaire d
    Next cel 'prochaine cellule de la boucle
    ReDim tt(d.Count) 'dimensionne le tableau tt()
    tt = d.keys 'récupère le dictionnaire sans doublon dans le tableau temporaire tt
    With Target.Validation 'prend en compte la validation de données de la cellule A3
        .Delete 'efface une éventuelle ancienne validation de données
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
           xlBetween, Formula1:=Join(tt(), ",") 'utilise la liste des éléments du tableau tt comme validation de données
    End With 'fin de la prose en compte de...
End With 'fin de la prise en compte de l'onglet "Tables"
Erase tt() 'efface le tableau tt
End Sub

Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
If Target.Address <> "$A$3" Then Exit Sub 'si la cellule active est différente de A3, sort de la procédure
If Target.Value = "" Then Target.Offset(1, 0) = "": Exit Sub 'si A3 est effacée, efface A4, sort de procédure
Target.Offset(1, 0) = "" 'efface la cellule A4
With Sheets("Tables") 'prend en compte l'onglet "Tables"
    dl = .Cells(Application.Rows.Count, 3).End(xlUp).Row 'définit la dernière ligne dl de la colonne 3 (=C)
    Set pl = .Range("C4:C" & dl) 'définit la plage pl
    .Range("B3").AutoFilter Field:=2, Criteria1:=CStr(Target.Value) 'filtre avec la valeur de A3 comme critère
    Set d = CreateObject("Scripting.Dictionary") 'définit le dictionnaire d
    For Each cel In pl.SpecialCells(xlCellTypeVisible) 'boucle sur toutes les cellules visibles cel de la plage pl
        d(cel.Offset(0, -1).Value) = "" 'alimente le dictionnaire d
    Next cel 'prochaine cellule de la boucle
    ReDim tt(d.Count) 'dimensionne le tableau tt()
    tt = d.keys 'récupère le dictionnaire sans doublon dans le tableau temporaire tt
    With Target.Offset(1, 0).Validation 'prend en compte la validation de données de la cellule A4
        .Delete 'efface une éventuelle ancienne validation de données
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
           xlBetween, Formula1:=Join(tt(), ",") 'utilise la liste des éléments du tableau tt comme validation de données
    End With 'fin de la prise en compte de l'onglet "Tables"
    .Range("B3").AutoFilter Field:=2 'affiche tout (supprime le critère)
End With 'fin de la prise en compte de l'onglet "Tables"
Range("A4").Select 'sélectionne la cellule A4
End Sub

Le fichier :

 

[mouftie]

Re : Créer une liste à partir / en fonction d'une autre

Bonjour Monsieur,
Merci pour votre aide et votre lien, mais je suis débutante et autodidacte et je ne sais toujours pas lire (comprendre) des formules compliquées, comme par exemple votre formule pour trier la liste des secteurs :

=INDEX(secteur;MIN(SI(secteur<>"";SI(NB.SI(J$1:J2;secteur)=0;LIGNE(INDIRECT("1:"&LIGNES(secteur)));LIGNES(secteur)))))

.

Je ne comprends pas non plus la fin de la formule que vous me donnez pour la liste des noms :

=DECALER(nom;EQUIV(A3;secteur;0)-1;0;NB.SI(secteur;A3))

en effet, NB.Si correspond à la partie hauteur de la fonction DECALER.
Merci pour votre aide.

 

[mouftie]

Re : Créer une liste à partir / en fonction d'une autre

Bonjour Robert,
Merci pour votre travail, il est vraiment bien documenté et clair.
Il y a pleins de choses nouvelles pour moi, je vais essayer de m'en inspirer.
Mille fois merci à tous.