Using Hyperlink

[yallaire64]

Hi,
I'm trying to put an adress in an hyperlink function, it works if I put an actual adress :
=Hyperlink(Cell("adress",Deviation!A2893),B5), this activate the cell Deviation!A2893

But if I want to have it more automatic... I would like to replace Deviation!A2893 with ==Adress(match(B5,Deviation!A2:A5297,0),1,1,1,"Deviation") that returns Deviation!$A$2893

Any input would be appreciated.

 

[FormR]

Hi, welcome to the forum!

You could try like this:

=HYPERLINK("#"&ADDRESS(MATCH(B5,Deviation!A:A,0),1,1,1,"Deviation"),B5)

 

[yallaire64]

Wow... only "#", I missed it somewhere in the MS help pages !!!

Thanks that worked, here is the final formula !!!
=Hyperlink("#" & Adress(Match(B5,Deviation!A2:A5297,0)+1,1,1,1,"Deviation"),"Click here")

 

[yallaire64]

I have another one on the subject...
How can I change Majors (in red below) with the value of cell F1 for instance.

=LIEN_HYPERTEXTE("#"&ADRESSE(EQUIV(A810,Majors!$A$2:$A$18012,0)+1,1,1,1,F$1),"OK")

 

[FormR]

Hi, this is how I would do it:

=HYPERLINK("#'"&F$1&"'!A"& MATCH(A810,INDIRECT("'"&F$1&"'!$A$2:$A$18012"),0)+1,"OK")

Which in French (my assumption about your language settings) would be:

=LIEN_HYPERTEXTE("#'"&F1&"'!A"& EQUIV(A810;INDIRECT("'"&F1&"'!$A$2:$A$18012");0)+1;"OK")

 

[yallaire64]

Thanks that worked perfectly.