MenuderoulantParOnglet

[gerson94]

Hello toutes et tous,

J'ai fichier Global et j'aimerais dans chaque onglet ne choisir que les descriptifs de l'onglet sélectionné. En gros, mon choix devrait être limité au descriptif du seul onglet choisi parce que derrière j'applique un recherche V et j'ai une liste trop longue.

J'espère que c'est clair

Merci d'avance

Gerson

 

[david84]

Re : MenuderoulantParOnglet

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

 

[gerson94]

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

 

[david84]

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

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+

 

[Iznogood1]

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.

 

[david84]

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+

 

[david84]

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 :

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+

 

[Iznogood1]

Re : MenuderoulantParOnglet

Merci, Davis,

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

 

[david84]

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

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

NBValeurs ​devient

=NB.SI(Parties;Onglet)

Validation devient

=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...

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

 

[Iznogood1]

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é...)

 

[david84]

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+

 

[gerson94]

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