Atteindre la dernière cellule d'une feuille avec un lien hypertexte

Cette astuce a un but plus pédagogique que pratique.
Il est en effet évident que la solution en VBA est plus propre et simple.
L'autre solution qui consiste à utiliser la combinaison CTRL + Flèche bas est aussi plus simple (attention cependant aux arrêt intempestifs sur les cellules vides).

Vue d'ensemble des formules :

Recherche de la dernière cellule vide

Tout d'abord, on se souviendra des deux formules suivantes qui permettent d'atteindre la dernière cellule contenant :
- Soit des nombres : =EQUIV(E1+307;Feuil1!$A:$A)
- Soit du texte : =EQUIV("*";Feuil1!$A:$A;-1)

On notera que si la dernière valeur est une erreur, elle n'est pas considérée.

En combinant les deux formules, on a ensuite la dernière valeur de la colonne, à condition que cette colonne contienne à la fois des nombres et du texte :
=MAX(EQUIV("*";Feuil1!$A:$A;-1);EQUIV(E1+307;Feuil1!$A:$A))
Sinon, on obtient l'erreur #N/A

On peut donc gérer l'erreur #N/A de la manière suivante :
=MAX(
SI(ESTNA(EQUIV("*";Feuil1!$A:$A;-1));0;EQUIV("*";Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(E1+306;Feuil1!$A:$A));0;EQUIV(E1+306;Feuil1!$A:$A)))

Créer la référence en toute lettre

Il s'agit ici de trouver la référence de la cellule sous la forme A1.
Pour cela, on utilise la fonction ADRESSE :
=ADRESSE(MAX(
SI(ESTNA(EQUIV("*";Feuil1!$A:$A;-1));0;EQUIV("*";Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(E1+306;Feuil1!$A:$A));0;EQUIV(E1+306;Feuil1!$A:$A)));1)
qui renvoit donc la valeur $A$6 dans notre exemple.

Création du lien hypertexte

On peut finalement créer un lien hypertexte pointant sur A6 à l'aide de la formule suivante :
=LIEN_HYPERTEXTE("#Feuil1!$A$6";"Pointe sur A6")

et donc, en reprenant la formule précédente, nous obtenons :
=LIEN_HYPERTEXTE(
"#Feuil1!" &
ADRESSE(MAX(
SI(ESTNA(EQUIV("*";Feuil1!$A:$A;-1));0;EQUIV("*";Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(E1+306;Feuil1!$A:$A));0;EQUIV(E1+306;Feuil1!$A:$A)));1);
"Dernière ligne")

qui transformera la cellule en lien hypertexte.

Un peu de simplification ...

Là, on peut "simplifier" un peu en utilisant un nom reprenant la position de la dernière cellule.

Pour cela, dans Insertion - Nom - Définir, on va créer le nom derniere_ligne et lui attribuer la formule suivante :
="#Feuil1!" &
ADRESSE(MAX(
SI(ESTNA(EQUIV("*";Feuil1!$A:$A;-1));0;EQUIV("*";Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(Feuil1!C3+306;Feuil1!$A:$A));0;EQUIV(Feuil1!C3+306;Feuil1!$A:$A)))
;1)

Il ne reste plus qu'à écrire la formule suivante :
=LIEN_HYPERTEXTE(derniere_ligne;"Dernière ligne")

Ces formules sont bien sur un peu trop complexes par rapport au poblème initial, mais elles permettent d'ouvrir un certain nombre de possibilités quand à la création de liens hypertextes dynamiques et de recherches de dernières cellules.

Source d'inspiration - entre autres :
AndrewExcelTip

Gaetan Mourmant
https://www.polykromy.com

Pour marque-pages : Permaliens.

2 réponses à Atteindre la dernière cellule d'une feuille avec un lien hypertexte

  1. Utilisateur Avancé d'Excel dit :

    Absolument génial !

    Je complète car qu'il suffit d'ajouter un "+1" juste à la fin de la formule, avant le point virgule de fin :
    la formule.... et juste avant la fin : +1;2)

    Et là, du coup, on atteint la première cellule VIDE d'une colonne.

    Merci.
    Je ne partage pas le discours concernant l'éventuelle "inélégance" du projet exposé ici.

    Car il arrive fréquemment que le recours à VBA soit rédhibitoire pour tout un tas de raisons aussi diverses que variées et propres à chacun.

    Au contraire, je trouve la présentation proposée ici claire, clean et réellement fonctionnelle.

    Merci à l'auteur.

  2. François PASQUALINOTTO dit :

    J'ajoute une question :
    Peut-on dire que, finalement, "EQUIV(E1+307;" sert à établir un format ?

    Et j'ajoute que j'ai scrupuleusement décortiquée et adaptée à mon besoin votre proposition et qu'en la combinant àce qui est présenté ici :

    https://www.excel-exercice.com/utiliser-les-images-dans-vos-formules-excel/?unapproved=76791&moderation-hash=3b75e55fdfd3fad73e5d81f2df4d6cca#comment-76791

    On obtient un lien hyper texte comme vous le proposez, mais qui, de plus, incorpore une image à la (ou aux) cellule considérée.

    C'est super.

Laisser un commentaire

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


La période de vérification reCAPTCHA a expiré. Veuillez recharger la page.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.