=HYPERLINK()

[polly]

What a nice site you have here! I've been trying to make a HYPERLINK type formula that once made can be dragged to 600 other cells to create hyperlinks.
(Example)
I have column of numbers at C2:C9 on sheet2 & a column of the same numbers in differing orders on sheet1 C2:C9, on sheet2,
In D2 ( and below) I would like a hyperlink to take me to the row on sheet1 that has the number reffered to on sheet2. Like this
In E2(sheet2)
=MATCH(C2,sheet1!$C$2:$C$9,0) which returns "5"
In F2 (sheet2)
=ADDRESS(E2+1,3,3,1,"sheet1") which
returns sheet1!$C6
sheet1!$C6 is right and this is where the link should point me to.
I've tried alot of different formula's and combo's of formula's, here are a couple
In sheet2 D2 I wrote:

=HYPERLINK(F2,"go there") or

=HYPERLINK(CELL("contents",F2),"go there")

Only to see<Can't open specified file>
The link and all is there but doesn't go to that cell on that sheet.
I think I am close but I am not sure.
Of course this is just an example but I don't want to have to insert hyperlink by hand 600 times for the real workbook.

Thank You in advance Paul
This message was edited by polly on 2002-04-06 18:19

 

[Yogi Anand]

Hi Polly:
in your formulation

=ADDRESS(E2+1,3,3,1,"sheet1") which
returns sheet1!$C6

for the hyperlink to work correctly,
include the Workbook name

so your formula would essentially become
="[Book1]"&ADDRESS(E2+1,3,3,1,"sheet1")
and return
[Book1]Sheet1!C6

HTH

Please post back if it works for you otherwise explain a liitle further ... and let us take it from there!





_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line
This message was edited by Yogi Anand on 2003-01-19 17:21

 

[polly]

Yogi Anand,
Works great. I put the book name in with the &(ampersand).
="[Book1.xls]"&ADDRESS(E2+1,3,3,1,"sheet1")

I was starting to think Excel 97 wouldn't do it, every time I think that I find out it is me that couldn't figure it out.
I was close to that at one time but the (&) was the key.

Thank you so very very much
Paul
!!!!!this is a very wonderful site!!!!!

 

[Yogi Anand]

Good Job Polly!
Now time to get some rest ... and let us start the fun again tomorrow morn!