La fonction Lien_hypertexte pour rendre des liens dynamiques

Excel permet d’insérer logiquement des liens Hypertextes. Mais comment faire pour utiliser des liens dynamiques ?Par exemple, je souhaite créer un lien vers la dernière ligne de mon tableau ou je veux renvoyer vers une feuille du classeur mais dont le nom est dynamique:

17-12-2015 14-24-18

Présentation

Dans l’exemple ci-dessus, j’ai créé deux types de liens hypertextes dynamiques:

  • La flèche verte en A1 renvoie sur la première cellule sous la colonne B. Ici, l’onglet est fixe (“#MENU!”) tandis que la cellule est variable (dépend du nombre de cellules dans la colonne B.
  • Les mois de la colonne C renvoient en cellule A1 (cellule fixe) dans l’onglet qui dépend du mois correspondant (Colonne B et même ligne). L’onglet est ici variable.

Et chacune des formules contient comme dernier argument le libellé à afficher. Voici comment doit être utilisée cette fonction:

=LIEN_HYPERTEXTE(emplacement_lien, [nom_convivial])

Le nom_convivial est facultatif. S’il n’est pas renseigné, le libelle sera le même que l’emplacement_lien.

Onglet Variable

L’onglet doit toujours être construit ainsi:

“#” & Nom_de_la_feuille & “!”

En fait l’onglet doit être entourée de # à gauche et de ! à droite. Si le nom de la feuille est variable, il faudra alors mettre la référence de la cellule qui contient le nom de la feuille. Dans notre cas, nous mettrons B4 pour le mois de janvier, B5 pour le mois de février et ainsi de suite. Il suffira d’écrire la formule une fois puis de la dupliquer jusqu’à décembre.

Si l’onglet est connu, il suffira alors de faire comme pour la première formule:

“#MENU!”

 

Cellule variable

Dans ce cas là qui correspond à la première formule, nous devrons utiliser la fonction ADRESSE, dont le fonctionnement pour notre cas est le suivant:

=ADRESSE(no_Ligne ; no_colonne)

 

Numéro de ligne

Il suffira donc de mettre le chiffre qui correspond à la ligne cible puis le chiffre qui correspond à la colonne.

Pour trouver la dernière ligne de notre tableau, nous devons compter le nombre de cellules dans la colonne B (NBVAL(B:B)) puis compter le nombre de cellules au-dessus (nous pouvons en voir 2):

17-12-2015 14-44-14

Enfin, il faudra ajouter 1 car nous voulons la première cellule APRES la dernière de la colonne. La formule pour obtenir le numéro de ligne sera donc:

NBVAL(B:B)+2+1      soit NBVAL(B:B)+3

Numéro de colonne

Ici, c’est très simple car nous le connaissons. En effet, nous souhaitons cibler la colonne B. Il suffit donc de connaître son alphabet (enfin jusqu’à ‘B’ !!!!) pour savoir qu’à B correspond la 2ème colonne.

Nous pouvons donc utiliser la formule suivante pour avoir une cellule dynamique:

ADRESSE( NBVAL(B:B)+3 ; 2 )

Libellé

Nous avons le choix:

  • Soit écrire en toutes lettres le libellé comme dans notre premier cas (“ê” qui devient une flèche vers le bas avec la police WINGDINGS, voir l’article “Créer des flèches dans une cellule”) ;
  • Soit avoir un libellé dynamique qui peut dépendre d’une autre cellule comme pour nos mois.

 

Conclusion

Au final, voici donc les deux formules que nous avons utilisées:

Dans la cellule A1:

=LIEN_HYPERTEXTE(“#MENU!” & ADRESSE(NBVAL($B:$B)+3;2);”ê”)

 

Dans les cellules C4 à C15:

=LIEN_HYPERTEXTE(“#” & B4 & “!A1”;B4)

 

Vous pouvez bien sûr aller plus loin en utilisant des liens vers d’autres fichiers. Vous pouvez ainsi créer vos propres menus…

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *