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+