XL 2013 affichage de la liste déroulante à partir d'une cellule à choisir [Résolu]

[bellenm]

Bonjour à tous, voilà plusieurs jours que je cherche la bonne formule sur une liste déroulante sous Excel.
• En fait ce que je cherche c’est de pouvoir afficher dans ma liste la première cellule en fonction d’une autre.
Je m’explique : Cellule « A » le nom d’un club répertorier également dans la même liste déroulante afin de ne pas avoir d’erreur d’écriture ! La cellule « B » devrait alors commencer sa liste déroulante à partir du nom du club sélectionner dans la cellule « A ».
Dans cette liste déroulante j’y ai mis le nom de club, suivit ensuite de leurs joueurs. Comme il y a ± 400 membres la liste est chaque foi longue à dérouler alors que si elle pouvait commencer par le nom du club choisi puis continuer la liste, donc de leurs joueurs, ce serait parfait.

• Deuxième question, sur ma colonne de donnée pour la liste déroulante comment faire pour modifier le texte, couleur – gras- barré, et retrouver cette mise en forme dans la liste déroulante ?
Est-ce clair ou dois-je développer ?
Merci pour votre aide.

Marc.

 

[CISCO]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Bonsoir à tous

Cf. en pièce jointe. Le travail n'est pas fini, mais j'ai fait quelques essais.

*Comme il y a beaucoup de joueurs, et qu'une liste déroulante proposant tous ces noms serait longue, donc peu pratique, j'ai opté pour une autre possibilité :
En DONNEES LIS!E2, il suffit de taper les premières lettres du nom du joueur pour que la liste déroulante ne propose que les noms commençant par ces lettres.
Cette liste utilise indirectement la liste des noms des joueurs en DONNEES LIS!L. Comme ce classement dans l'ordre alphabétique est obtenu grâce à une formule matricielle, il consomme beaucoup de temps de calcul à chaque modification sur cette feuille. Par conséquent, pour gagner du temps, j'ai fait afficher ces résultats une fois, puis je n'ai laissé le calcul matriciel qu'en L2 et L3 et ai fait un copier-collage spécial valeurs sur le reste de la plage. Autrement dit, les noms sont là, au bon endroit, mais si on modifie les noms dans la colonne DONNES LIS!A, la colonne DONNEES LIS!L ne sera pas actualisée en conséquence. Pour le faire, il faut étendre la formule matricielle en L3 vers le bas.

*En feuille CAL, j'ai dû passer par des petites macros :
- Pour les remises à zéro sur les plages H3:N6 et H8:N11 (A ma connaissance, ce n'est pas faisable avec des formules)
- Pour connaitre la position de la cellule active, sélectionnée dans H3:N6 ou H8:N11, adresse que j'ai fait afficher en S1. En effet, j'ai besoin de cette information pour savoir quelle plage on est en train de remplir, H3:N6 ou H8:N11. Normalement, on peut le faire avec la formule =CELLULE("adresse") en S1 ou directement dans les formules matricielles en dessous, dans cette colonne S. Mais dans ce cas, la mise à jour ne se faisait pas automatiquement. Si on cliquait sur J4 par exemple, il fallait cliquer dans la barre de formule et valider avec la touche "entrer" pour que la mise à jour se fasse. Pas très pratique. J'ai donc utilisé une macro pour avoir cette adresse en S1.

Voici ces macros, sur la feuil4 Alt+F11, double-clic sur Feuil4(CAL) pour voir les codes)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Stockage cellule pointée dans cellule S1
[S1] = Target.Address(0, 0) ' Cellule pointée
End Sub

Sub Effacer3()
Range("H3:N6").Select
    Selection.ClearContents
    'Range("A8").Select
    ActiveWorkbook.Save
End Sub

Sub Effacer8()
Range("H8:N11").Select
    Selection.ClearContents
    'Range("A8").Select
    ActiveWorkbook.Save
End Sub

Effacer3 pour effacer la plage H3:N6 et Effacer8 pour effacer la plage H8:N11.

Fait quelques essais sur ces deux plages et vois si cela fonctionne comme tu veux.

Pour créer les autres plages jaunes, en dessous, ce ne sera pas très compliqué :
* Il suffira de copier par ex la plage H8:O11, en englobant donc la remise à zéro à droite,
* et de la coller en dessous autant de fois que désiré,
* de copier la macro Effacer8 et de la coller autant de fois que désirée sur la feuille VBA (Alt+F11),
* de changer son nom en Effacer13, Effacer18 et ainsi de suite,
* et de changer les plages dans ces nouvelles macros (H13:N16 et ainsi de suite).

Il faudra ensuite attribuer ces nouvelles macros aux diverses remises à zéro (clic droit sur la remise à zéro destinatrice puis "Affecter une macro", puis choisir la macro correspondante, EFFACER13 par ex, dans la liste).

@ plus

P.S : Comme je ne m'y connais que très très peu en macro, j'ai cherché sur le forum les macros utilisées sur le fichier en pièce jointe. Pour l'adresse de la cellule active en S1, j'ai fait avec https://www.excel-downloads.com/threads/extraire-le-nom-de-la-cellule-pointee.191984/ sur un fil de Gicebe.

 

[bellenm]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Bonsoir CISCO,

Super le travail déjà fait.

*En feuille CAL, j'ai dû passer par des petites macros :
- Pour les remises à zéro sur les plages H3:N6 et H8:N11 (A ma connaissance, ce n'est pas faisable avec des formules)

Simplement en effacent les cellules ca ne vas pas ou y a t'il des formules?

Pour la cellule "S1" chez moi elles indique une mauvaise cellule!
comme sur la feuille le tableau avec l'insertion des nom est en "I3:O6" et non "H3:N6"

je viendrais revoir ce tableau demain c' est mieux que je l'imaginais.

Les noms dans la liste déroulante serait plus facile (afin de ne pas se tromper) si elle est suivant le cassement

On se parle demain
Bonne nuit CISCO

Marc

 

[CISCO]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Bonsoir

Bonsoir CISCO,

Super le travail déjà fait.
Simplement en effaçant les cellules ça ne va pas. Où y a t'il des formules?

Il ni y a pas de formule dans les cellules jaunes, juste une liste déroulante. Pour voir sa définition, faire comme d'habitude, après avoir sélectionné une de ces cellules jaunes --> Données -->Validation de données
Regarde la pièce jointe 948879
Dans ce cas, tu vois que la liste déroulante est définie grâce au nom listealphaintermédiaire, donc en passant par le gestionnaire de nom, on trouve que listealphaintermédiaire = DECALER(CAL!$T$3;;;NB.SI(CAL!$T:$T;"><"&"")-1)
Regarde la pièce jointe 948880, c-à-d le haut de la colonne CAL!T qui liste dans l'ordre alphabétique les noms des joueurs du club en CAL!B1 non encore cités dans la plage jaune en cours.

Pour la cellule "S1" chez moi elle indique une mauvaise cellule!

Est-ce que tu travailles sur le même fichier que moi ? Si oui, en S1 tu devrais avoir la bonne adresse. Un exemple ci-dessous pour L4
Regarde la pièce jointe 948881

comme sur la feuille le tableau avec l'insertion des nom est en "I3:O6" et non "H3:N6"

ce qui semblerait montrer que tu ne travailles pas avec le même fichier que moi.

Les noms dans la liste déroulante serait plus facile (afin de ne pas se tromper) si elle est suivant le classement

Si tu y tiens, c'est facile à faire...

@ plus

 

[bellenm]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Bonjour CISCO,

Voici le fichier alléger, je l'ai mis par listeintermediaire mais la liste ne se vide pas comme toi, les noms 'enlève oui mais pas l'espace vacant dans la liste déroulante

Après je peux modifier en rajoutant des colonnes ou les déplaçant ?
pour une mise en page plus facile ?

 

[CISCO]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Bonjour

Il faut définir les listes déroulantes avec listealphaintermédiaire et pas avec listeintermédiaire (c-à-d avec le haut de la colonne T et pas le haut de la colonne S).

Si tu ne veux pas cette liste dans l'ordre alphabétique, mais uniquement éliminer les cellules vides de la colonne S, il faut écrire en matriciel en T4

=SIERREUR(INDEX(S$1:S$24;PETITE.VALEUR(SI(S$4:S$24<>"";LIGNE(S$4:S$24);1000);LIGNES($2:2)));"")

et bien sûr tirer cette formule vers le bas aussi longtemps que nécessaire.

@ plus

 

[CISCO]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Bonjour

Une autre possibilité en pièce jointe :
Comme le tri des noms des joueurs dans l'ordre alphabétique avec une formule matricielle prenait beaucoup de temps de calcul sur la feuille DONNEES LIS, j'ai cherché sur le forum une macro et ai fait avec une macro de J. Boisgontier (que je remercie au passage) (mais j'y comprends rien à sa macro, mais alors, vraiment rien (Cf. module 3 dans la page VBA)).
Il ni y a donc plus une colonne avec les clubs et une autre avec les joueurs, mais une seule, la DONNEES LIS!H, classant les premiers au début (à cause des *) puis les joueurs.
Avantage : les calculs sont presque instantanés et la mise à jour se fait sans aucun problème. Tout terme écrit dans la plage DONNEES LIS!A2:A600 est immédiatement classé au bon endroit dans la colonne DONNEES LIS!H.

Il m'a donc fallu redéfinir les noms club, joueursalpha, nbremembres...

Au passage, j'ai vu que certains clubs n'étaient pas écrits de la même manière en colonne A et en colonne C : espace en plus au milieu (SOCOLIE EDF et SOCOLIE EDF), accent circonflexe dans l'un et pas dans l'autre (GRACE et GRÂCE)... Attention à ce genre de "petites" fautes qui peuvent poser de "gros" problème avec Excel.

@ plus

 

[bellenm]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

re,

Oui cette version est bien plus rapide!

sur la feuille "DONNEES LIS" en "E2" pour la recherche club sa ne fonctionne pas est ce normal.

Si non parfait n'aurais plus que la mise en page à finir

Super CISCO

Marc

 

[bellenm]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

re,

j'essaye de copier en vain les colonnes "G, H, I, J" de la feuille "DONNEES LIS" du fichier {AC GRACE menuneuf} sur la feuille d'un fichier avec des feuilles corriger et bonne sur un classeur {AC GRACE menu} et les cellules ne sont pas bonne j'ai partout des #NOM?

Comment recopier cette feuilles et ses formules macro ou autre?

Ou dois je faire l'inverse et copier les feuilles bonne sur ce classeur (l'actuel avec"CAL" ) quand se sera bon?

Marc

 

[CISCO]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Re

Dans AC GRACE menuneuf, il faut :
* Aller dans la feuille VBA avec Alt+F11,
* Double-cliquer sur Module 3,
* Copier toute la macro
* Aller dans ton fichier
* Alt+F11
* Dans l'arborescence à gauche correspondant à ton fichier, clic droit --> Insertion --> Module
* Dans le module vierge qui vient de s'ouvrir, coller la macro (Ctrl+V).
* Retourner sur la feuille où tu veux faire le tri
* Sélectionner toute la plage où tu veux faire ce tri
* Ecrire =sansdoublonstrié(A$2:A$600) (A$2:A$600 désignant la plage où se trouvent les noms à classer. J'ai pris 600 au pif, pour que tu puisses rajouter des joueurs en colonne A)
* Valider en matriciel avec Ctrl+maj tempo+entrer toute la plage d'un coup (et pas juste la cellule du haut de la colonne)
* Copier les formules qui sont en AC GRACE menuneuf G!2:H2
* Les coller juste à coté de ta colonne triée dans ton fichier
* Etendre ces deux formules vers le bas par ex en double-cliquant sur la poignée noire en bas à droite après avoir sélectionné ces deux formules
* Aller dans le gestionnaire de noms et modifier les noms club, joueursalpha, listeintermédiairesansblanc, NbMembres

Cela fait pas mal de taf... mais c'est un bon moyen pour apprendre comment ça marche. Si tu n'y arrives pas, j'essayerai de le faire sur un fichier simplifié mais ayant le bon nombre de feuilles, les colonnes au bon endroit, mais beaucoup moins de lignes.

@plus

 

[bellenm]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

re CISCO,

Je suis pas programmeur j'ai trouver plus ou du moins aussi long: je recopier les feuilles et modifier tous les liens et surtout sauvegarder!

Ca y est presque et ce grâce à toi

Je suis passé par le forum j'ai pas trouver comment créer une macro qui m'imprimerait la feuille avec comme nom le N° mach "A096" sur cette feuille qui placerais le fichier dans un répertoire qui serait nommé: "RILTT"

As tu une solution?

Encore Merci pour tout

Marc

 

[CISCO]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Bonsoir

Et non, ça je ne sais pas faire.

@ plus

 

[bellenm]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Bonjour Cisco,

Pas grave tu as déjà beaucoup participer sur mon projet.

Dés qu'il serra clos je t'enverrais un lien pour regarder.
On peut le mettre en téléchargement, mais cela pourrait correspondre pour un club ne jouant qu'avec deux équipes à la fois et trois contre trois dont les ordre de rencontre sont identique.

Maintenant cela pourrait être construit sous combox enfin avec des boites de dialogue etc...

Marc

 

[CISCO]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Bonjour

Vu le nombre de changements que nous avons fait, tu as tout intérêt à vérifier le contenu des noms définis. Pour cela, une manière de faire intéressante :
* Aller dans le gestionnaire de noms
* cliquer sur le nom à "ausculter"
* cliquer sur sa définition en bas de la fenêtre
La plage correspondante apparaît alors entourée de pointillés.

* Club doit comprendre l'ensemble des clubs,
* joueursalpha à l'ensemble des joueurs, dans l'ordre alphabétique
* listealphaintermédiaire ou listeintermédiairesansblanc à l'ensemble des joueurs, pas encore dans les cellules jaunes, de l'équipe citée en B1,
* Nbmembres l'effectif de tous les clubs, en colonne I...

@ plus

 

[bellenm]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Re CISCO,

sur la dernière feuille, la neuf formule super rapide! mais me manque deux joueurs !
Dans la base colonne "A,B,C" je peux rajouter à la dernière ligne de nouveau inscrit et trier après sur la colonne c, b a ?
ou comment faut il faire pour ajouter d'autre joueur

Ci joint le fichier avec les nom dans la première colonne S sont bien mis mais pas dans la T ni sur la liste déroulante!

Merci pour la correction CISCO

Marc

 

[CISCO]

Re : affichage de la liste déroulante à partir d'une cellule à choisir

Re CISCO,

sur la dernière feuille, la neuf formule super rapide! mais me manque deux joueurs !

En T3 il y avait SIERREUR(INDEX(S$1:S$19;PETITE.VALEUR(SI(S$4:S$19<>"";LIGNE(S$4:S$19);1000);LIGNES($2:2)));""), donc seuls les noms dans la plage S4:S19 était transposés dans la colonne T. Il fallait SIERREUR(INDEX(S$1:S$56;PETITE.VALEUR(SI(S$4:S$56<>"";LIGNE(S$4:S$56);1000);LIGNES($2:2)));""). En mettant 56, cela sous-entend que tu as au maximum 52 joueurs par club (56-4 = 52). Si ce n'est pas le cas, il faudra mettre un nombre encore plus grand à la place des 56 dans cette formule et tirer les formules dans les connes S et T encore plus bas. Quand j'aurai le temps, je vérifierai si les plages définies dans les différentes formules sont assez grandes.

Dans la base colonne "A,B,C" je peux rajouter à la dernière ligne de nouveau inscrit et trier après sur la colonne c, b a ?
ou comment faut il faire pour ajouter d'autre joueur

Normalement, si tu rajoutes des données en dessous de la ligne 431, cela se rajoute automatiquement dans les colonnes G, H... et dans les plages nommées correspondantes.

mais pas dans la T ni sur la liste déroulante!

Cf. l'erreur signalée ci-dessus (la plage s'arrêtant en ligne 19).

Marc[/QUOTE]