Valeur variable dans formule de cellule (numero de ligne)

Bonjour

J'utilise une formule dans une cellule qui doit m'afficher le lien hypertexte (url web) contenu dans la cellule d'un tableau placé sur une autre page (Prospection).
J'ai dans un premier temps une fonction VBA qui se charge de récupérer tous les liens de la colonne B de cette page "Prospection"

Function ExtraireLien(cellule As Range) As String
    On Error Resume Next
    ExtraireLien = cellule.Hyperlinks(1).Address
End Function

Je l'appelle dans ma formule de cellule comme suit :

=ExtraireLien(Prospection!B13)

Ca fonctionne très bien, et la cellule qui récupère le lien présenté sur le ligne 13 colonne B affiche bien le résultat attendu.
En revanche, si j'utilise la valeur d'une autre cellule (I2) qui affiche le n° de ligne (puisqu'il varie selon d'autre parametres) sur cette même formule en écrivant comme suit :

=ExtraireLien("Prospection!B"&I2)

le résultat me renvoie #VALEUR! dans ma cellule.

Comment expliquer que la première formule fonctionne alors que la seconde qui utilise une variable (qui dépend de la valeur de I2) ne fonctionne pas ? Est-il possible que I2 ne soit pas encore disponible au moment ou la formule de la cellule de récupération soit déjà lancée ? Ce problème est-il courant ou ai-je droit à un traitement de faveur ? Merci beaucoup pour votre aide.

Bonjour,

et si tu tapes dans une cellule ceci ;

=INDIRECT("Prospection!B"&I2)

est-ce que ça renvoie bien le lien ?

oui ça renvoie bien la bonne valeur de cellule (le texte correspondant à la bonne ligne). Mais finalement, ce INDIRECT peut il me récupérer plutôt le lien Hypertexte intégré au nom contenu dans la cellule ("Prospection!B"&I2) ?

Je pensais que c'est ça que tu aurais, pourrais-tu dire ce que ça renvoie et ce que tu voudrais avoir à la place ?

Le INDIRECT me renvoie le texte de la cellule. Par exemple, dans cette colonne du tableau, j'affiche des noms d'entreprises qui sont "le plus souvent" accompagnés d'un lien sur le site web correspondant (le lien est intégré en tant que lien hypertexte et donc non visible dans la cellule).

Dans l'idéal, je cherche à récupérer dans une autre feuille, la valeur de ces cellules, dans la colonne B de la page Prospection (avec une variable contenue dans une cellule I2 (sur la feuille "Fiche ID" correspondant au numéro de ligne à récupérer selon la fiche ouverte).

Bref, ce que je souhaite récupérer c'est le lien hypertexte et non pas le texte de la cellule (que je récupère déjà dans une autre partie de la fiche).

Ma formule sans variable I2 fonctionne très bien, mais une fois que je souhaite intégrer la valeur I2 en tant que n°ligne (ex : "Prospection!B"&I2), ça ne fonctionne plus. J'ai pourtant l'impression que c'est la bonne syntaxe non ?

Si besoin de plus de clareté, je monterai un petit fichier fictif qui synthétise le probleme. Merci en tout cas ;)

Et en combinant les deux, est-ce que ça marche ?

=ExtraireLien(INDIRECT("Prospection!B"&I2))

Concernant la syntaxe du VBA, c'est toujours un peu différent d'une formule mise dans une cellule.

Sinon un petit fichier fictif serait en effet utile.

ohhh Bravo ! C'est effectivement le INDIRECT qui vient de régler le problème. Lorsque que je fait fonctionner la formule sans variable I2 mais avec une cellule fixe, je n'ai pas besoin du INDIRECT, mais si je ne met pas de INDIRECT avec ma formule utilisant la valeur I2, çà ne fonctionne pas. C'est vraiment génial de voir marcher ce truc, enfin ! Tu me sauves la mise une fois de plus, même si l'enjeu n'est pas professionnel, je vais pouvoir avancer sur d'autres points.

Une question au cas ou, une fois que j'ai ce lien récupéré dans ma cellule avec la fonction magique =ExtraireLien(INDIRECT("Prospection!B"&I2)), y'a t'il un moyen de le rendre cliquable (hypertexte). Et si oui, y'a t'il moyen de l'intégrer plutôt à une icon ou unicar pour ne pas afficher le lien mais un picto cliquable contenant ce lien ?

Je vais regarder un peu pour faire des tests, mais si une solution simple existe je suis preneur. Belle soirée et encore un grand merci à toi !

Pour le rendre cliquable, je crois que ceci devrait aller ;

=LIEN_HYPERTEXTE(ExtraireLien(INDIRECT("Prospection!B"&I2));"nom à afficher")

Pour l'image il doit y avoir moyen mais je ne sais pas comment.

Merci ! C'est effectivement fonctionnel comme çà. Sans passer par le VBA, cette formule peut elle contenir une condition qui renvoie ("") au lieu de ("nom à afficher") si la valeur de la cellule ne contient aucune info ?

Pour ce qui est de l'utilisation d'une image à la place de l'info "nom à afficher", j'ai placé un UNICAR et çà fonctionne ;)

=LIEN_HYPERTEXTE(ExtraireLien(INDIRECT("Prospection!B"&(I2+4)));UNICAR("128000"))

Je pensais à &SI.NON.DISP("";"") à la fin de ma formule mais ça n'a pas l'air d'être ça.

Peut-être comme ça ;

=SI(INDIRECT("Prospection!B"&I2)="";"";LIEN_HYPERTEXTE(ExtraireLien(INDIRECT("Prospection!B"&I2));UNICAR("128000")))

Merci, je viens de la tester mais c'est toujours l'image unicar qui apparait même quand la cellule donne un résultat vide. J'ai pourtant bien vérifié que ma cellule était vraiment vide et non pas renseignée comme #N/A ou "0" mais c'est vraiment "" qui est en résultat quand y'a pas de lien récupéré. J'avais également testé ESTVIDE et ça n'a pas marché non plus. Penses tu qu'il est possible de contourner ce probleme en mentionnant dans le SI : "ne commence pas par http" (SI <> "http*..... ) ?

Le souci vient probablement du fait qu'il y a quelque chose dans la cellule qui renvoie du vide, mais la cellule n'est alors pas considérée comme vide.

Il y a plusieurs moyens de contourner ça, je crois que ceci devrait aller ;

=SI(NBCAR(INDIRECT("Prospection!B"&I2))=0;"";LIEN_HYPERTEXTE(ExtraireLien(INDIRECT("Prospection!B"&I2));UNICAR("128000")))

Elle ne donne aps plus de résultat mais je viens de comprendre pourquoi. En fait, il y a toujorus une valeur "texte" présente sur Prospection!B"&I2 puisque toutes les cellules de ce tableau sont remplies. En revanche, tous les nom de boites ne sont pas associées à un lien hypertexte, elles ne renvoient donc pas d'info dans la cellule $C$2 de ma feuille "Fiche ID"

En vérifiant que Prospection!B"&I2 est vide, je tombe forcement toujours sur une valeur existante. il faudrait plutot que je procède en lancant d'abord le INDIRECT("Prospection!B"&I2), et ensuite que je donne mes condition selon ce qui s'affiche dans la cellule de réception du résultat ($C$2). Ca sent le VBA, ça me semble compliqué de faire tout çà sur une seule formule ?

Je pense qu'on peut se passer de VBA, essaye ceci ;

=SI(NBCAR(LIEN_HYPERTEXTE(ExtraireLien(INDIRECT("Prospection!B"&I2))))=0;"";LIEN_HYPERTEXTE(ExtraireLien(INDIRECT("Prospection!B"&I2));UNICAR("128000")))

Mais comment te remercier ??? ça fonctionne à merveille et j'en apprends chaque jour un peu plus. Je vais analyser un peu cette formule, je crois qu'elle va m'être utile pour d'autres utilisation dans ce même projet. Un grand merci à toi et belle journée :)

De rien !

Pour me remercier, il suffit de cliquer en bas à droite de mon message précédent pour signaler que le sujet est résolu.

Merci beaucoup ! c'est fait ;)