XL 2010 Création de listes déroulantes interactive

[jeanmi]

J’ai un onglet « liste articles »

Dans lequel, j’ai 3 colonnes, A, B et C avec des références, des désignations, en C des familles. Les plages A2 :C36 sont nommées « table articles », peut-être pas utile ?

J’ai dans l’onglet « Bon de Cde »

En colonne A, de A27 : A57 des références qui s’afficheront en fonction du choix sélectionné au niveau de désignation (colonne C) et qui ne s’affiche que s’il y a une quantité dans la colonne D, ça fonctionne.

J’ai en colonne B des familles de produit, à partir d’une liste déroulante.

Ce que je voudrais faire, si possible :

Actuellement la liste déroulante qui est affichée au niveau de désignation, en colonne C, prend en compte toutes les données qui sont définies dans l’onglet « liste articles »

Es qu’il est possible que la liste déroulante qui est au niveau de désignation, colonne C, n’affiche que les désignations qui correspondent à la famille sélectionnée au niveau de famille, colonne B ?

J’ai fait beaucoup d’essais, sans arrivé à trouver une solution.

On me dit peut-être tableau croisé dynamique, mais là je ne maitrise pas ni dans le cas d’une solution VBA.

Il faut que je puisse, simplement, par la suite ajouter des informations complémentaires à l’onglet liste articles. Peut-être que la construction de mon fichier n'est pas la bonne ?

Si joins mon fichier d’essai.

Merci pour votre aide.

Cordialement

 

[job75]

es que je peux ajouter des données au niveau de kla liste des articles ?

Pour obtenir une mise à jour de la liste de validation Liste il faut ajouter cette macro dans la feuille "Bon de Cde" :

Private Sub Worksheet_Activate()
Worksheet_SelectionChange ActiveCell 'lance la macro quand on revient sur cette feuille
End Sub

 

[laurent950]

Bonsoir :

aprés avoir cherché.

Procédure : Créer une liste déroulante dynamique pour Famille et Désignation
- Classeur Excel : 2 Onglets (Bon_de_Cde) et (Liste_aricles) / Pas d'espaces dans les noms d'onglets !

Créer la liste Famille sans doublons
- Procédure pour créer une liste Famille sans doublons Famille en Colonne E.
* Copier la colonne C:C de Feuille liste_articles
* Coller la colonne copier en E1
* Selection de la colonne E:E
* Données / Supprimer les doublons / OK
* Résultat : toutes les familles uniques sont listées en E sans doublons

Suite :
Céer une liste Famille dynamique depuis le Gestionnaire de noms : Avec DECALER + NBVAL
* Formules / Gestionnaire de Noms / Nouveau
° Nom : Famille
° Zone : Classeur
° Formule : =DECALER(Liste_articles!$E$2;;;NBVAL(Liste_articles!$E:$E)-1)
° Fermer la boite de dialogue --->> la liste Famille devient dynamique (s’adapte si on ajoute des familles)

Suite :
Créer la Validation de donnée sur la feuille : Pour Famille
* Bon de commande en Cellule B27
° Donner / Validation des données
° Options
- Autoriser : Liste
- Sources : =Famille
- Ok
- Résultat : la liste déroulante propose toutes les familles uniques et dynamiques

Suite :
Créer la Validation de donnée sur la feuille : Pour Désignation en fonction de la Famille
* Bon de commande en Cellule C27
° Donner / Validation des données
° Options
- Autoriser : Liste
- Sources : =DECALER(Liste_articles!$B$2:$B$360;EQUIV(B27;Liste_articles!$C$2:$C$360;0)-1;0;NB.SI(Liste_articles!$C$2:$C$360;B27))
- Ok

' Maintenant Cela est fonctionnel :

Explication des fonctions :
* Formule : =DECALER(Liste_articles!$E$2;;;NBVAL(Liste_articles!$E:$E)-1)
° DECALER signifie “déplacer et créer une plage”.
° Cellule_de_départ → point de départ de la plage -->> (ici $E$2)
° Lignes → combien de lignes tu veux descendre depuis le départ -->> (ici vide = 0)
° Colonnes → combien de colonnes à droite ou à gauche -->> (ici vide = 0)
° Hauteur → nombre de lignes dans la plage finale -->> (Ici NBVAL(Liste_articles!$E:$E)-1)
* NBVAL(E:E) → compte toutes les cellules non vides de la colonne E de la plage de l'onglet (Liste_articles)
* -1 → on enlève la cellule d’en-tête (E1) pour ne pas l’inclure dans la liste
* Résultat : la plage va automatiquement s’étendre ou se réduire si on ajoute ou supprime des Familles.
° Largeur → nombre de colonnes dans la plage finale -->> (ici par défaut 1)
et
* =DECALER(Liste_articles!$B$2:$B$360;EQUIV(B27;Liste_articles!$C$2:$C$360;0)-1;0;NB.SI(Liste_articles!$C$2:$C$360;B27))
° DECALER signifie “déplacer et créer une plage”.
° Cellule_de_départ → point de départ de la plage -->> (ici Liste_articles!$B$2:$B$360) / 360 Cellules étendre si besoins
° Lignes → combien de lignes tu veux descendre depuis le départ -->> (ici ici EQUIV(B27;Liste_articles!$C$2:$C$360;0)-1)
* EQUIV(B27;Liste_articles!$C$2:$C$360;0) → trouve la première ligne où apparaît la Famille choisie en B27 dans la colonne C
* -1 → ajuste la position pour que DECALER commence exactement au bon endroit
° Colonnes → combien de colonnes à droite ou à gauche -->> (ici vide = 0)
° Hauteur → nombre de lignes dans la plage finale -->> (Ici NB.SI(Liste_articles!$C$2:$C$360;B27))
* NB.SI(Liste_articles!$C$2:$C$360;B27) → compte combien de lignes correspondent à la Famille choisie
* Résultat → la plage contient toutes les Désignations de cette Famille et s’adapte automatiquement si on ajoute ou supprime des lignes dans la colonne C
° Largeur → nombre de colonnes dans la plage finale -->> (ici par défaut 1)

 

[jeanmi]

re,
Explications dans le fichier.
Cordialement

re,
Explications dans le fichier.
Cordialement

pourquoi dans le fonction qui est au niveau des menue déroulant

=DECALER('liste articles'!$C$2;EQUIV(B27;_Famille;0);;NB.SI(_Famille;B27))

ce n'est pas l_Famille mais _Famille, juste pour comprendre ?

Merci

 

[gbinforme]

Pour obtenir une mise à jour de la liste de validation Liste il faut ajouter cette macro dans la feuille "Bon de Cde" :

Comment se fait-il que les lignes suivantes du bon ne s'affichent pas et ne s'impriment pas ?

 

[job75]

Le texte en colonne C est masqué par une MFC si la cellule en colonne D est vide.

 

[goube]

Bonsoir,

pourquoi dans le fonction qui est au niveau des menue déroulant

=DECALER('liste articles'!$C$2;EQUIV(B27;_Famille;0);;NB.SI(_Famille;B27))

ce n'est pas l_Famille mais _Famille, juste pour comprendre ?

Merci

l_Famille correspond à la liste des familles du tableau t_Famille servant de base aux listes déroulantes faisant appel à ces données, alors que _Famille correspond à la colonne Famille du tableau table_articles servant à la formule de calcul utilisant la fonction DECALER. Ce qui permet de tenir compte des données éventuellement rajoutées à ce tableau.

 

[jeanmi]

Bonsoir,

l_Famille correspond à la liste des familles du tableau t_Famille servant de base aux listes déroulantes faisant appel à ces données, alors que _Famille correspond à la colonne Famille du tableau table_articles servant à la formule de calcul utilisant la fonction DECALER. Ce qui permet de tenir compte des données éventuellement rajoutées à ce tableau.

Bonjour @goube à tous,

J'ai essayé de mettre en application dans mon fichier et je rencontre des petits problèmes.
J'ai mis une image des deux menus formule, qui sont différents ?

Celui du haut pour mon fichier et celui du bas pour ton fichier qui fonctionne bien.

Merci pour l'aide.

Cordialement

 

[goube]

Bonjour,
Est ce que le nom _Famille correspond bien à la colonne Famille du tableau table_articles ?

En cas de problème, si tu le peux, mets ton fichier.
Cordialement

 

[job75]

Bonjour le forum,

Il y avait des espaces superflus dans la liste de validation en colonne C de la feuille "liste articles", je les ai enlevés.

Par ailleurs je trie alphabétiquement la liste de validation Liste et je supprime les doublons éventuels, le code dans "Bon de Cde" :

Private Sub Worksheet_Activate()
Worksheet_SelectionChange ActiveCell 'lance la macro quand on revient sur cette feuille
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim crit$, P As Range
Application.ScreenUpdating = False
Me.Unprotect "jlejle"
With Range("C27:C" & Rows.Count)
    .Validation.Delete 'RAZ
    If Intersect(ActiveCell, .Cells) Is Nothing Then GoTo 1
    crit = ActiveCell(1, 0)
    If crit = "" Then crit = "*"
    With Sheets("liste articles").ListObjects(1).Range 'tableau structuré
        .AutoFilter 'désactive le filtrage s'il existe
        .Columns(5).Clear 'RAZ
        .AutoFilter 3, crit 'filtrage de la 3ème colonne
        Set P = .Columns(2).Offset(1).SpecialCells(xlCellTypeVisible)
        .AutoFilter 'désactive le filtrage s'il existe
        P.Copy .Cells(1, 5) 'copier-coller
        .Columns(5).Sort .Columns(5), xlAscending, Header:=xlNo 'tri alphabétique
        .Cells(1, 5).CurrentRegion.RemoveDuplicates 1, Header:=xlNo 'supprime les doublons
        .Cells(1, 5).CurrentRegion.Name = "Liste" 'plage nommée
        ActiveCell.Validation.Add xlValidateList, Formula1:="=Liste"
    End With
End With
1 Me.Protect "jlejle"
End Sub

A+

 

[gbinforme]

Le texte en colonne C est masqué par une MFC si la cellule en colonne D est vide.

Bonjour,
Merci effectivement ce classeur ésotérique est très dissuasif pour l'utilisateur avec une protection illusoire !

 

[job75]

La protection n'est guère gênante et n'a pas d'importance.

 

[jeanmi]

Bonjour,
Est ce que le nom _Famille correspond bien à la colonne Famille du tableau table_articles ?
Regarde la pièce jointe 1226656
En cas de problème, si tu le peux, mets ton fichier.
Cordialement

 

[jeanmi]

Bonjour @goude, à tous,

Dans mon fichier au niveau du tableau table_articles, la colonne D, zone D3😀xx n'a pas de nom dans mon fichier, ni dans le tient, il me semble ?

Moi, j'ai ajouté une colonne E pour les prix, donc mon tableau avec le nom liste_articles est bien B3/Exx et en G les familles avec les catégories nommées comme dans ton fichier. es que je peux changer le nom en G2, famille par liste_famille pour ne pas avoir deux colonne famille ?

Pour t'envoyer mon fichier, il faut que je supprime beaucoup de données, donc si vraiment besoin, je vais le faire, mais si je peux comprendre pourquoi ça ne fonctionne pas, donc, ce que je n'ai pas bien fait se serait bien.

Merci

Cordialement

 

[goube]

Re,
Les colonnes sont dans deux tableaux différents, pas de problème. Mais si tu veux changer, pourquoi pas.
Dans mon tableau le nom _Famille correspond à table_articles[Famille] ou D3 : D38. Pour vérifier dans la zone nom sélectionne _Famille.

Cordialement