Recherche de données dans une feuille dont le nom de l'onglet est renseigné cellule

[chris6999]

Bonjour le FORUM et joyeuses fêtes à tous

Je suis confrontée à un casse tête que e ne sais pas par quel bout prendre (à considérer que la solution existe…)

J'ai des onglets par mois où sont affichées les dates du mois (plage E7 à AA7)
Puis sur chaque ligne, par groupe de travail, j'ai le nom des agents et leur ville.
Une X est positionnée lorsque l'agent du groupe (pour chaque groupe) est de permanence.

Dans ma feuille consultation:
Je souhaiterais faire remonter en fonction du groupe et de la date saisie dans D6, le nom de l'agent de permanence et la ville (c'est à dire là où il y a une croix).
La recherche se faisant dans la feuille correspondant au mois (qui lui remonte dans D5).

Je pensais à une formule (peut-être INDEX ou SOMMEPROD je ne sais plus trop..) mais cela peut aussi être une macro.

Je mets un fichier en PJ qui précise mieux ma problématique.
Si l'un d'entre vous a des pistes sur le sujet je suis preneuse car là, il faut bien l'avouer je suis confrontée aux limites de ma nullité.

Merci d'avance à ceux qui sont restés au chaud et qui mettent leur savoir à disposition des néophytes comme moi.

Cordialement

 

[Modeste]

Re : Recherche de données dans une feuille dont le nom de l'onglet est renseigné cell

Bonjour chris6999,

Avec 3 plages nommées "groupe", "ville" et "agent", ça semble fonctionner 😕 ... si j'ai bien compris!

 

[chris6999]

Re : Recherche de données dans une feuille dont le nom de l'onglet est renseigné cell

Bonsoir modeste cela semble foncionner à merveille.
En revanche j'ai un peu de mal à comprendre la formule magique.

Les formules ne font référence à aucune zone ni à aucun critère.
Je crois que tu as travaillé dans les zones nommées en y incrémentant des fonctions mais je ne parviens pas à les lire.

Pourrais-tu m'en dire un peu plus?
Car j'ai essayé d'adapter ta solution sur mon fichier réel et, bien sûr, rien ne se passe.

Merci d'avance

Bonjour chris6999,

Avec 3 plages nommées "groupe", "ville" et "agent", ça semble fonctionner 😕 ... si j'ai bien compris!

 

[Modeste]

Re : Recherche de données dans une feuille dont le nom de l'onglet est renseigné cell

Bonsoir,

Le début du parcours, c'est > Onglet Formules > Gestionnaire de noms ... c'est là que tu verras les formules associées aux 3 plages.
Je suis parti du principe que dans les feuilles janvier, février, etc. les groupes seront toujours triés en colonne A.

Pour trouver la ville, il est possible de simplifier la formule des zones nommées, si pour un groupe, la ville est toujours la même (dans ton exemple, c'est le cas, mais dans la réalité ... toi seule le sait 🙂)

 

[chris6999]

Re : Recherche de données dans une feuille dont le nom de l'onglet est renseigné cell

Bonjour

Après avoir fouiné un peu partout, pas moyen de trouver l'option "gestion des noms" dans l'onglet "FORMULES" (qui n'est pas proposée en Excel 2003)
Je pense que ma version d'Excel ne me propose pas cet accès facilité pour intervenir sur les zones nommées.

Pour récupérer les formule j'ai fait F3 puis coller la liste. Cela donne ceci:

groupe
=DECALER(INDIRECT(Consultation!$D$5&"!$A$7");EQUIV(Consultation!$C18;INDIRECT(Consultation!$D$5&"!$A$8:$A$44");0);EQUIV(Consultation!$D$6;INDIRECT(Consultation!$D$5&"!$E$7:$AA$7");0)+3;NB.SI(INDIRECT(Consultation!$D$5&"!$A$8:$A$44");Consultation!$C18))

agents
=DECALER(INDIRECT(Consultation!$D$5&"!$A$7");EQUIV(Consultation!$C18;INDIRECT(Consultation!$D$5&"!$A$8:$A$44");0);2;NB.SI(INDIRECT(Consultation!$D$5&"!$A$8:$A$44");Consultation!$C18))

ville
=DECALER(INDIRECT(Consultation!$D$5&"!$A$7");EQUIV(Consultation!$C22;INDIRECT(Consultation!$D$5&"!$A$8:$A$44");0);1;NB.SI(INDIRECT(Consultation!$D$5&"!$A$8:$A$44");Consultation!$C22))

Mes interrogations :
Par curiosité, à quoi font référence à Consultation!$C18, Consultation!$C22,
Ces références semblent varier en fonction de la cellule sélectionnée lorsque je génère la liste

Lorsque je mets ces formules directement dans la feuille elles ne remontent que des #N/A

Pourrais tu me faire un copier coller de ces trois formules s'il te plaît ? Peut-être que ce que je récupère ne correspond pas à ce que tu as écrit.
En effet je souhaiterais élargir ma plage $A$8:$A$44 en $A$8:$A$200.
Mais lorsque je remplace :$A$44 par :$A$200 cela ne fonctionne plus.

C'est rallant, être si près du but et ne pas pouvoir exploiter ta solution.

Pour info on ne peut simplifier la formule car il peut y avoir plusieurs villes pour un même groupe

Merci d'avance pour ton aide

 

[Modeste]

Re : Recherche de données dans une feuille dont le nom de l'onglet est renseigné cell

Salut 🙂

Désolé, je n'avais pas vu que tu étais en XL 2003 😱
Pour autant qu'il m'en souvienne, comme disait l'autre, tu devrais trouver l'équivalent dans
menu Insertion > Nom > Définir...

Tu n'as pas dit si, pour un groupe, les villes étaient toujours les mêmes ... ou pas!?

En 2 mots (mais, si besoin, j'essaierai de faire un chouïa plus détaillé!) la formule utilisée pour nommer la plage groupe redéfinit une zone correspondant, dans la feuille "janvier" ou "février", à la colonne où figure la date cherchée à hauteur de la première occurrence du groupe cherché et d'un nombre de lignes correspondant au nombre d'occurrences de ce même groupe en colonne A.

Ainsi, pour la formule qui figure en Consultation!E9, si le 8 janvier est la date à trouver, le nom "groupe" correspondra à la plage janvier!J8:J13 (c'est parce que la fonction renvoie une plage que les formules, utilisées comme telles dans des cellules, donnent une erreur!)

Quant à ton Consultation!C18, c'est le dernier argument de la fonction NB.SI, dans le cas présent.

... Tu me suis? ... Un peu, beaucoup, passionnément, etc.?

 

[chris6999]

Re : Recherche de données dans une feuille dont le nom de l'onglet est renseigné cell

Bonjour Modeste,

C'est avec un grand intérêt que j'ai pris connaissance de ta réponse et je pense avoir compris le principe même si ce dernier est bien au-delà de mes connaissances actuelles.

Mon problème c'est cette histoire de formule comme zone nommée que je ne sais pas par quel bout prendre.

En Excel 2003 il est très compliqué de modifier quoi que ce soit dans les zones nommées car la fenêtre est minuscule et on ne peut pas circuler à l'intérieur sans changer les données.
Mon seul recours est de sortir la formule en copiant la liste mais du coup cela modifie les critères selon où se situe mon curseur.

Du coup je ne sais pas à quoi font références tes formules et il m'est impossible d'adapter à mon cas.


"Tu n'as pas dit si, pour un groupe, les villes étaient toujours les mêmes ... ou pas!?"
Dans mon précédent message j'ai précisé qu'il pouvait y avoir plusieurs villes pour un même groupe.
Pour être plus précise :
Le groupe 1 sera connu sur 5 villes (tj les mêmes)
Le groupe 2 sera connu sur 3 villes (tj les mêmes)
Le groupe 3 sur 2 villes (tj les mêmes)
Le groupe 4 sur 2 villes (tj les mêmes)
Le groupe 5 sur 3 villes (tj les mêmes)

Je te remercie en tous cas pour prendre le temps de m'expliquer. Même si je ne vais pas au bout du projet (car cela me semble de plus en plus compromis vu la complexité de la chose) j'aurais appris de nouvelles fonctionnalités.

Bonne journée
Cordialement





















Et à ce propos il n'est pas possible de définir de plages fixes dans les feuilles mois car les tailles de groupe peuvent évoluer et les lignes bouger...

 

[Modeste]

Re : Recherche de données dans une feuille dont le nom de l'onglet est renseigné cell

Allons, pas de défaitisme 🙂 ... décomposons la formule

Intéressons-nous à la zone nommée "agent" et à sa formule, si nous sommes en E9 de la feuille Consultation:

=DECALER(INDIRECT(Consultation!$D$5&"!$A$7");EQUIV(Consultation!$C9;INDIRECT(Consultation!$D$5&"!$A$8:$A$44");0);2;NB.SI(INDIRECT(Consultation!$D$5&"!$A$8:$A$44");Consultation!$C9))

... dans un premier temps faisons comme si tout devait être trouvé dans la seule feuille de janvier (ce qui supprime déjà les INDIRECT de la formule). Celle-ci devient donc:

=DECALER(janvier!$A$7;EQUIV(Consultation!$C9;janvier!$A$8:$A$44;0);2;NB.SI(janvier!$A$8:$A$44;Consultation!$C9))

DECALER prend 5 arguments, sous la forme: DECALER(réf, lignes; colonnes; [hauteur]; [largeur]) (les 2 derniers sont facultatifs)
Vois les choses comme si on "ciblait" quelque chose avec l'objectif d'un appareil photo: on prend un point de départ réf, on monte ou on descend de x lignes et on se décale vers la droite ou la gauche de y colonnes; éventuellement, on "agrandit" la sélection d'une certaine hauteur et d'une certaine largeur (comme si on zoomait). Le résultat étant ce que tu vois, au terme de l'opération, sur l'écran de ton appareil.
Dans l'exemple ci-dessus, ref est la cellule A7 de la feuille janvier (notre point de départ)
Pour E9, on devra donc repérer, en feuille janvier les agents correspondant au "groupe 1". Au départ de A7, on va descendre d'un certain nombre de lignes, jusqu'à trouver la première occurrence de groupe 1 en colonne A de janvier. Ce que permet
EQUIV(Consultation!$C9;janvier!$A$8:$A$44;0) résultat, dans le cas présent: 1 autrement dit, au départ de A7, si on descend d'une ligne, on "pointe" sur A8.
Si ce qu'on veut cibler, ce sont les agents, de combien de colonnes doit-on se décaler -toujours au départ de A7- pour trouver la colonne des agents? de 2 colonnes (pour la plage "ville", la formule sera identique, si ce n'est qu'on ne décalera que d'une seule colonne vers la droite)
Pour déterminer combien d'agents son concernés par le "groupe 1" (ce qui donnera la hauteur), il suffit de compter, en colonne A de janvier, le nombre d'occurrences de ce "groupe 1":
NB.SI(janvier!$A$8:$A$44;Consultation!$C9) résultat, dans le cas présent aussi: 6
La largeur ne doit pas être précisée ici: elle est de 1 colonne dans le cas présent.
La formule serait donc =DECALER(janvier!$A$7;1;2;6), ce qui correspond à la plage janvier!C8:C13

J'essaierai de voir, de mon côté si on ne pourrait pas s'y prendre autrement

 

[Modeste]

Re : Recherche de données dans une feuille dont le nom de l'onglet est renseigné cell

Bonjour,

Pour info (ou pour la postérité!?) il y a bien une autre manière d'y arriver, qui ressemblera peut-être moins à une séance de torture, à condition qu'on puisse, dans chaque feuille mensuelle, utiliser une colonne supplémentaire, pour y insérer une formule.
Cette manipulation peut se faire:
- simultanément dans les feuilles de janvier à décembre
- dans une feuille existante, pour être ensuite copiée-collée dans les autres feuilles
- dans une feuille "modèle" qu'on dupliquerait ensuite en 11 exemplaires

En AD8 (par exemple):

=SI($D$1<>Consultation!$D$5;"";SI(DECALER($E$8:$E$44;;EQUIV(Consultation!$D$6;$E$7:$AB$7;0)-1)="X";LIGNE();""))

... à recopier vers le bas. Cette formule affichera, dans la seule feuille du mois concerné par la recherche et pour chaque groupe, le n° de la ligne ou figure un "X", dans la colonne correspondant à la date sélectionnée en feuille Consultation.

---

Sur base de ces n° de lignes, dans la feuille "Consultation", en D9:

=INDEX(INDIRECT($D$5&"!B1:B44");PETITE.VALEUR(INDIRECT($D$5&"!AD8:AD44");LIGNES($1:1)))

... et en E9, sa petite sœur:

=INDEX(INDIRECT($D$5&"!C1:C44");PETITE.VALEUR(INDIRECT($D$5&"!AD8:AD44");LIGNES($1:1)))

... Les deux sont à recopier sur les 4 lignes suivantes.

Terminez 2014 dans la bonne humeur 😀