Créer un lien en fonction de l'index d'une cellule

Bonjour à tous,

Préambule : aucune données personnelles ici, les nom, e-mail et numéro de téléphone ont été inventés.

Fichier de travail :

Je cherche à créer un LIEN_HYPERTEXTE dans Excel en fonction de l'index d'une cellule :

- J'ai un onglet "Eleves" avec une liste d'élève. Chaque élève est rattaché à une école
- J'ai un onglet "Ecoles" avec la liste des écoles, comprenant leur e-mail et numéro de téléphone

Je souhaiterai que lorsque je clique sur la cellule correspondant à l'école d'un élève, je sois redirigé sur mon onglet "Ecoles" et que la bonne plage soit sélectionnée.
Je souhaite également que le nom qui s'affiche pour le lien soit celui de l'école, ainsi, si je dois modifier le nom de l'école, je n'ai à le faire qu'à un seul endroit.

Jusque là, rien de très compliqué : "=LIEN_HYPERTEXTE("#Ecoles!$A$2:$E$2"; Ecoles!$C$2)"

Oui mais, je souhaite que mes écoles soient affichées triées par type (Publique ou Privée) puis par ordre alphabétique. (Trier et filtrer -> tri personnalisé -> Trier par "type" puis par "Nom")

Lorsque j'applique le tri, les informations dans la colonne "Ecole" de l'onglet "Eleve" sont complètement chamboulé :
Par exemple, "BARTHELEMY Paul" est à l'école "SAINT JOSEPH", une fois le tri appliqué, il se retrouve à l'école "ORVEAUX".

J'ai eu pour idée de rajouter une colonne "index" dans mon onglet "Ecoles", ainsi, je n'aurait plus qu'à faire référence au numéro de l'index pour créer mon lien.

C'est là que je coince, et voici ma question :

Comment créer un LIEN_HYPERTEXTE faisant référence au numéro d'index dans la colonne "A" de mon onglet "Ecoles" pour que lorsque je clique depuis l'onglet "Eleves" je soit redirigé sur la bonne école ?

Le fichier pouvant être amené à évoluer (nouveaux élèves venant de nouvelles écoles), l'idée finale est de simplement rajouter une nouvelle école avec un nouveau numéro d'index incrémenté, puis de réappliquer le tri par type d'école puis par nom, sans avoir à refaire manuellement tout mes liens, ni à trier moi même mes nouvelles écoles (très chronophage sur plusieurs milliers de lignes).

J'ai bien réussi à récupérer le nom de l'école en faisant RECHERCHEV sur un numéro d'index : "=RECHERCHEV(1;Ecoles!$A:$G;3;0)"

Toutefois, je ne sais pas comment récupérer l'adresse de la cellule et l'imbriquer dans LIEN_HYPERTEXTE.

Merci d'avance du temps que vous prendrez à me lire et à me répondre.

Cordialement,

Snite

Bonsoir,

J'ai ajouté la colonne E qui contient le lien Hypertexte et la colonne F qui contient le N° de ligne de l'école correspondante dans la feuille "Ecoles", ainsi vous pouvez faire tous les tris que vous souhaitez.

Ces 2 colonnes sont grisées pour signifier quelles contiennent des formules, donc à ne pas toucher, simplement à cliquer sur le lien hypertexte.

Formule en E2:

=LIEN_HYPERTEXTE("#'Ecoles'!C"&$F2; $D2)

Formule en F2:

=EQUIV($D2;Ecoles!$C:$C;0)

Cdlt

Bonsoir,

Arturo83 bonsoir,

Vous pouvez n'avoir qu'une colonne, et pour répondre à la demande : "sur mon onglet "Ecoles" et que la bonne plage soit sélectionnée." la formule pourrait être :
=LIEN_HYPERTEXTE("#Ecoles!A"&EQUIV($D2;Ecoles!$C:$C;0)&":E"&EQUIV($D2;Ecoles!$C:$C;0); $D2)
en colonne E.

Encore bravo à vous, ne serait-ce pour la compréhension de la demande, je ne l'avait pas celle-là !

@ bientôt

LouReeD

Bonjour,

C'est vrai que j'aurai pu simplifier la formule, je n'ai même pas pensé à creuser davantage.

Bravo à vous pour cette amélioration.

Cdlt

Bonsoir Arturo83 et LouReeD,

Merci pour vos réponses. Je pourrais essayer à partir du lundi 27 vos propositions.

Je vous ferais un retour à ce moment là.

Merci encore, je vais regarder la définition de la formule EQUIV en attendant.

Belle soirée à vous.

Bonsoir,

rendons à César ce qui appartient à César ! Je n'ai rien fait !

@ bientôt

LouReeD

Bonjour,

J'ai pu avancer grâce à vos éléments de réponses, et j'y suis presque.

Je ne souhaite pas ajouter de colonnes dans l'onglet "Eleves", d'où l'ajout de la colonne "INDEX" dans l'onglet "Ecoles".

Je réussis bien à sélectionner la bonne ligne peut importe le tri appliqué sur l'onglet "Ecoles" :

=LIEN_HYPERTEXTE("#Ecoles!A"&EQUIV(2;Ecoles!$A:$A;0)&":E"&EQUIV(2;Ecoles!$A:$A;0);)

La dernière étape concerne le texte de mon lien HYPERTEXTE :

Je souhaite récupérer ce texte dans la colonne C de l'onglet "Ecoles", mais j'ai le texte de la formule qui s'affiche lorsque j’essaie d'appliquer la formule EQUIV :

=LIEN_HYPERTEXTE("#Ecoles!A"&EQUIV(3;Ecoles!$A:$A;0)&":E"&EQUIV(3;Ecoles!$A:$A;0); "#Ecoles!C"&EQUIV(3;Ecoles!$A:$A;0))

Étant débutant sur Excel, je ne suis pas familier avec la syntaxe, pourriez vous me dire ce qui ne va pas ?

Merci d'avance !

P.S : Les colonnes "A SUPPRIMER" sont juste là pour un rappel visuel des écoles et des index, je ne m'en sert pas dans la formule et elles seront enlevées dans le fichier final.

Bonjour,

Un essai voir le fichier joint

Slts

Bonjour boss_68,

Merci ! Cela fonctionne correctement !

J'ai légèrement modifié ta formule pour me baser sur l'index que je connais et que je rentre manuellement (exemple avec MELBOURG dont l'index est 3) :

=LIEN_HYPERTEXTE("#Ecoles!A"&EQUIV(3;Ecoles!$A:$A;0)&":E"&EQUIV(3;Ecoles!$A:$A;0); RECHERCHEV(3;Ecoles!$A:$C;3;FAUX))

Voici le résultat dans un fichier.

Merci à tous pour vos contributions !!

Snite