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

Question

Bonjour, 

Après plusieurs recherches infructueuses, je décide d'exposer mon problème ici, merci d'avance de votre aide.

J'ai un fichier excel avec 3 feuilles dans celui-ci.
- feuille1: c'est ma feuille de travail, c'est dans cette feuille que j'ai plusieurs Colonnes ou les données proviennent des feuilles 2 et 3 (sous forme de liste déroulantes et ensuite rechercheV pour les données liées.

-Feuille2: Carnet d'adresses des clients (Nom, Prénom, données.....)
-Feuilles3: Les biens des clients (avec une colonne NOM qui est une liste de choix de la colonne Nom de la feuille2)   Je sais ainsi quel bien appartient a qui (les biens sont uniques!!)


Donc dans la Feuille1 je sélectionne le client (liste de choix de la colonne Nom de la feuille2) et ensuite je sélectionne son bien (liste de choix de la colonne bien de la feuille3).

Ce que j'aimerais, c'est de n'avoir dans ma liste déroulante, que les bien de la personne sélectionnée seulement et pas les autres.... sinon la liste est kilométrique et porte parfois a confusion.


Est-ce possible ? si oui comment , quel type de fonction ? ou paramètre....


Merci

Kris



Configuration: Windows XP / Safari 535.7

JvDo · Accepted Answer

Bonsoir,

J'ai cherché une solution sans utiliser de cellules intermédiaires (avec des noms à formule matricielle) mais je n'ai pas trouvé.
Donc voilà une solution qui utilise une zone de la feuille Feuil1!$A$3:$A27 pour calculer la liste déroulante associée au nom du client saisi en Feuil1!$B$3 (sur la base d'une validation qui utilise Feuil2!nom).
La table des biens est en Feuil3!$C$5:$D$28, 1ère colonne pour les noms et 2ème colonne pour les biens.

En  Feuil1!$A$3, tu saisis =SI(LIGNES(Feuil1!$A$3:A3)<=NB.SI(Feuil3!$C$5:$C$28,Feuil1!$B$3),INDEX(Feuil3!$D:$D,PETITE.VALEUR(SI(Feuil3!$C$5:$C$28=Feuil1!$B$3,LIGNE(Feuil3!$D$5:$D$28),9^9),LIGNES(Feuil1!$A$3:A3))),"") que tu valides par CTR+MAJ+ENTER et que tu recopies sur Feuil1!$A$3:$A27.

En Feuil1!$C$3 (ou ailleurs) tu fais ta 2ème validation qui pointe sur Feuil1!$A$3:$A27 et tu auras ainsi la liste des biens détenus par le nom que tu auras saisi en Feuil1!$B$3.
j'utilise un nom dynamique biens_du_nom avec la formule =DECALER(Feuil1!$A$3,,,NB.SI(Feuil3!$C$5:$C$28,Feuil1!$B$3),1) qui permet de limiter la liste de validation au nombre de biens que le client détient.

https://www.cjoint.com/?BAmttaeJ8hf

cordialement

PS : j'ai fait la maquette en version 2010 et j'ai eu des tonnes d'alertes de compatibilité pour le format 97-2003. J'espère que ça fonctionne

Vaucluse · Answer

Bonjour 
peut être en partant sur ce principe, la colonne code devant comporter la formule qui détectera les lignes à retenir?
mais il faudrait en savoir plus sur vos données pour adapter, si vous n'y parvenez pas
https://www.cjoint.com/?BAmq0Zu7vj9
A noter pour qu'une liste déroulante soit applicable avec une référence hors de la feuille, il faut la nommer.
C'est dans l'affectation du nom qu'il faudra placer la formule qui , dans le modèle, sert directement à la validation.
Revenez si besoin avec un  petit bout de votre modéle, en remplaçant les noms si confidentialité.
crdlmnt

Vanx_ · Answer

Bonjour, 


merci pour les réponses, en fait, c'est JvDo qui a tout à fait compris ce que je voulais faire! merci pour la réponse. 

Le seul problème c'est que je dois faire cela en A4 B4, A5 B5, A6 B6, ... j'ai donc pensé qu'il faut peut-être récupérer la valeur de la ligne pour savoir la fournir à la formule..... '=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)

Merci pour votre aide.

JvDo · Answer

Bonjour,

Il faut revoir la structure de la feuille (https://www.cjoint.com/?BAnkBfRmkQZ pour afficher une liste par ligne.
Si ça se complique, il faudra s'orienter vers un bout de code VBA.

Je me pose quand même une question sur ce que tu veux faire complètement.
En effet, si tu enrichis ta demande au fil de l'eau, tu vas peut-être passer à côté d''une solution simple (genre un filtre sur un fichier à plat) et te retrouver avec des rustines.
Ceci dit, si ça te convient comme ça, c'est parfait.

Cordialement

Vanx_ · Answer

Bonjour,

Je vais épurer mes données et vous poster mon Excel, ça sera plus compréhensible je pense. Il faut juste me laisser le temps de faire une copie et de poster ça.

je pensais aussi au VBA mais je n'ai jamais imbriqué de code dans une feuille Excel. à voir donc.

michel_m · Answer

bonjour

si tes listes déroulantes sont des listes à validation de données regarde
la feuille 1 cascadexl+recherche
https://www.cjoint.com/?3AnkML85emG

en espèrant que....

Vanx_ · Answer

Bonjour,


http://cjoint.com/?0AnqgUIssUu

Voici un exemple recréé car mon fichier est trop complexe, si on suit ma logique, il faut sélectionner un client (liste CLIENT) (Jaune) et puis sélectionner un bien (Liste BIEN) du client (Orange)... dans mon cas réel, la liste bien est très grande et beaucoup d'info. Donc je ne souhaite avoir les bien que du client précédemment sélectionné.

voir donc feuille BIENS et CLIENTS pour comprendre

Ma page donnée, elle sert a faire du publipostage (courrier, rapport, ...)

Voilà.

Vanx_ · Answer

Bonjour, 

N'ayant plus de nouvelles, je remets le lien de mon exemple. 

http://cjoint.com/?0AnqgUIssUu

Je ne sais pas si mon problème est clair? 


merci de votre aide.

JvDo · Answer

Bonjour,
Je t'ai déjà répondu dans mon dernier post qu'une solution est de mettre les listes de validation en ligne plutôt qu'en colonne.
Si  tu as des centaines de biens par client, ça va faire beaucoup de colonnes, c'est sûr.
Si en plus tu as beaucoup de listes de validation les unes en dessous des autres, ta feuille va grossir énormément.

Une autre approche est de mettre quelques lignes de code derrière la feuille de saisie à base de listes de validation.
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

ensuite tu ajoutes un module et tu y places :

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
Tu vois dans le début du code que tu as besoin de définir des noms dans tes feuilles :
dans la feuille d'association clientsxbiens :
Noms_Assoc : la zone des noms
Biens_Assoc : la zone des biens en regard
dans ta feuille de saisie,
Biens_Validation : la zone de tes saisies des biens
Client_Validation : la zone de tes saisies des clients
dans ta feuille des clients,
nom : la zone liste de tes noms de clients 

cordialement

vanx_ · Answer

Bonjour,

Je ne connais pas Excel sous cet angle là (VBA) donc je vais commencer par de petits morceaux de codes puis je vais intégrer le total pour voir. je ferais celà courant de semaine prochaine comme je suis a d'autres tâches pour le nomment.

Encore merci de votre aide JVDO.

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

10 réponses

Discussions similaires