MenuderoulantParOnglet

D

david84

XLDnaute Barbatruc

• 25 Juin 2015

• #2

Re : MenuderoulantParOnglet

Bonjour,
ci-joint une proposition par formules à adapter de ton côté.
A+

 

Pièces jointes

• MenuderoulantParOnglet.xlsm

  21.1 KB · Affichages: 40
• MenuderoulantParOnglet.xlsm

  21.1 KB · Affichages: 45
• MenuderoulantParOnglet.xlsm

  21.1 KB · Affichages: 45

Vote positif 0 Vote négatif

G

gerson94

XLDnaute Occasionnel

• 25 Juin 2015

• #3

Re : MenuderoulantParOnglet

Bonjour David,
Merci pour ton retour. C'est pas mal 🙂, mais j'ai un souci c'est que mon fichier comporte plus de 300 lignes et les Parties ou Domaines sont beaucoup plus nombreux sans compter les "Descriptifs". Du coup là ou ça coince c'est le fait de dupliquer les Parties avec le nom des descriptifs. Je souhaitais une formule intégrée qui prenne seulement en compte directement la Colonne B de l'onglet Global...si c'est pas trop demander
Je ne sais pas si je suis clair.

Merci

 

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 25 Juin 2015

• #4

Re : MenuderoulantParOnglet

gerson94 à dit:

Bonjour David,
Merci pour ton retour. C'est pas mal 🙂, mais j'ai un souci c'est que mon fichier comporte plus de 300 lignes et les Parties ou Domaines sont beaucoup plus nombreux sans compter les "Descriptifs". Du coup là ou ça coince c'est le fait de dupliquer les Parties avec le nom des descriptifs. Je souhaitais une formule intégrée qui prenne seulement en compte directement la Colonne B de l'onglet Global...si c'est pas trop demander
Je ne sais pas si je suis clair.

Merci

Cliquez pour agrandir...

Avec une seule formule cela ne me semble pas jouable en l'état. Ci-joint une proposition via VBA à tester et à adapter à ton propre fichier.
A+

 

Pièces jointes

• test_menu_deroulant.xls

  52 KB · Affichages: 31

Dernière édition: 26 Juin 2015

Vote positif 0 Vote négatif

Iznogood1

XLDnaute Impliqué

• 26 Juin 2015

• #5

Re : MenuderoulantParOnglet

Bonjour,

tout est faisable par formule, ce qui évite de passer par VBA (plus dynamique, pas d’événement à gérer, pas de code qui plante...)

L'astuce est de créer des noms dynamiques.

Déterminer les parties disponibles :
Parties =DECALER(Global!$B$2;0;0;NBVAL(Global!$B:$B)-1)

Idem pour les descriptifs :
Descriptif =DECALER(Global!$C$2;0;0;NBVAL(Global!$B:$B)-1)

Savoir dans quel onglet on se trouve :
Onglet =STXT(CELLULE("nomfichier");TROUVE("]";CELLULE("nomfichier"))+1;NBCAR(CELLULE("nomfichier"))-TROUVE("]";CELLULE("nomfichier")))

Déterminer le nombre de parties qui correspondent à l'onglet :
NBValeurs =SOMMEPROD((Parties=Onglet)*1)

Générer une matrice {1;2;...} jusqu'au nombre de valeurs calculées plus haut :
Matrice =LIGNE(INDIRECT("$A$1:$A$" & NbValeurs))

Générer la liste des valeurs correspondantes
Validation =INDEX(Descriptif;PETITE.VALEUR((Parties=Onglet)*LIGNE(Parties);NB.SI(Parties;"<>" & Onglet)+Matrice)-1)

Et enfin, dans chaque onglet, faire une validation des données par liste, avec
Source =DECALER(Validation;0;0;NbValeurs)
Attention, ceci ne fonctionne que si la liste de l'onglet Parties est classée par parties....


Voir le fichier joint.

 

Pièces jointes

• MenuderoulantParOnglet_SansVBA.xlsx

  16.2 KB · Affichages: 27
• MenuderoulantParOnglet_SansVBA.xlsx

  16.2 KB · Affichages: 40
• MenuderoulantParOnglet_SansVBA.xlsx

  16.2 KB · Affichages: 42

Dernière édition: 28 Juin 2015

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 26 Juin 2015

• #6

Re : MenuderoulantParOnglet

Bonsoir,
Excellent travail Iznogood1 !
Bonne idée de passer par des noms dynamiques.
Pour ma part j'ai modifié le fichier du message #4 qui comportait un bug sans que je n'aie pu en détecter la raison.
J'ai donc collé les plages dans un nouveau fichier et cela semble fonctionner sans encombre.
J'en ai profité pour rendre les plages dynamiques et remplacé RECHERCHEV par INDEX+EQUIV (je préfère).
A+

 

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 26 Juin 2015

• #7

Re : MenuderoulantParOnglet

Je m'aperçois que j'ai oublié de préciser que pour que le menu déroulant soit créé automatiquement en colonne B il fallait préalablement tirer la formule placée colonne C.
Si l'on veut que le menu déroulant soit créé colonne B même si la cellule de la colonne C ne comporte pas de formule c'est évidemment faisable et même plus simple : remplacer dans le module ThisWorkbook l'ancien code par :

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Global" Then
  On Error Resume Next
  If Not Intersect(Target, [B:B]) Is Nothing Then Call Creer_liste_onglet
End If
End Sub

A+

 

Dernière édition: 28 Juin 2015

Vote positif 0 Vote négatif

Iznogood1

XLDnaute Impliqué

• 28 Juin 2015

• #8

Re : MenuderoulantParOnglet

Merci, Davis,

Attention, je n'avais pas joint le bon fichier.
Je viens de le remettre à jour.

 

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 28 Juin 2015

• #9

Re : MenuderoulantParOnglet

De rien.

Ta solution fonctionne à condition bien sûr que la colonne des parties soit triée de manière à ce que les items de la colonne Descriptif soient regroupés (et non affichés de manière disparate comme c'est le cas dans le fichier du demandeur).

On peut peut-être simplifier les formules des noms dynamiques :
Parties et Onglet ne changent pas.
Descriptif devient

Code:

=DECALER(Global!$C$1;0;0;NBVAL(Global!$B:$B))

NBValeurs ​devient

Code:

=NB.SI(Parties;Onglet)

Validation devient

Code:

=INDEX(Descriptif;PETITE.VALEUR(SI(Parties=Onglet;LIGNE(Parties));Matrice))

A+

Edit : ceci dit s'il faut que la colonne des parties soit triée on n'a plus besoin de tous les noms : Onglet (pour raccourcir la formule) et Parties (pour rendre la plage dynamique) suffisent il me semble...

Code:

=DECALER(Global!$C$1;EQUIV(Onglet;Parties;0);;NB.SI(Parties;Onglet))

 

Dernière édition: 29 Juin 2015

Vote positif 0 Vote négatif

Iznogood1

XLDnaute Impliqué

• 29 Juin 2015

• #10

Re : MenuderoulantParOnglet

En effet, c'est beaucoup plus élégant !

(en fait je cherchais à ce que ça fonctionne même sans trier ; mais j'ai séché...)

 

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 29 Juin 2015

• #11

Re : MenuderoulantParOnglet

Apparemment la fonction DECALER n'accepte que des plages physiques, d'où la difficulté (voir l'impossibilité) d'utiliser cette fonction pour délimiter une matrice ramenée par un nom dynamique.
Ci-joint une possibilité en triant les données par formules, plage que l'on peut ensuite traiter pour obtenir les menus déroulants.
A+

 

Pièces jointes

• MenuderoulantParOnglet_SansVBA.xlsx

  20.9 KB · Affichages: 23
• MenuderoulantParOnglet_SansVBA.xlsx

  20.9 KB · Affichages: 34
• MenuderoulantParOnglet_SansVBA.xlsx

  20.9 KB · Affichages: 37

Vote positif 0 Vote négatif

G

gerson94

XLDnaute Occasionnel

• 30 Juin 2015

• #12

Re : MenuderoulantParOnglet

Hello

Merci à tous de vous êtes penchés sur mon problème. Je vais appliquer les différentes approches sur mon grand fichier et je vous dis comment çà marche.
Merci beaucoup. 🙂

Gerson

 

Vote positif 0 Vote négatif