Quick creation of hyperlinks

M

Mike B

A colleague has a spreadsheet with 170 entries which he
wants to link to the equivalent cell on another sheet in
the same book.

A number of us have tried to solve this for him by using
the =HYPERLINK function combined with things like &ROW()
to produce the adjusted cell ref.

We seem to produce a result you would expect to work but
get a "Cannot open specified file" error message.

I have tried putting in the full path showing the file's
location on our network with no greater success.

This is the formula he was using in cell B3 of his SOURCE
sheet:

=HYPERLINK(Updates!B3,"click here for update")

but it produces the error message described with or
without the path name.

Can anyone suggest a solution, please? Does the function
NEED the full path, or am I barking up the wrong tree?

TIA
 
M

Mike B

Andrew

Thanks for your suggestion - it doesn't seem to adjust the
references as you fill down, unless I am doing something
wrong. I can't say I understand why there is a # preceding
the sheet name.

However, with a bit more tinkering around, we have come up
with the following which, for interest, does seem to work:

=HYPERLINK("[Book1.xls]Updates!B"&ROW(),"Click here for
update")

Filling this down updates the row reference successfully.

Thanks once again for your time.

Mike B
 
D

Dave Peterson

Another way:

=HYPERLINK("#"&CELL("address",'Updates'!b3),"Click here")

Might be better when/if you change that workbook's name.

Mike said:
Andrew

Thanks for your suggestion - it doesn't seem to adjust the
references as you fill down, unless I am doing something
wrong. I can't say I understand why there is a # preceding
the sheet name.

However, with a bit more tinkering around, we have come up
with the following which, for interest, does seem to work:

=HYPERLINK("[Book1.xls]Updates!B"&ROW(),"Click here for
update")

Filling this down updates the row reference successfully.

Thanks once again for your time.

Mike B
-----Original Message-----
Try this.

=HYPERLINK("#Updates!B3","clickhereforupdate")

Hope it works for you.
Click to expand...
Click to expand...
 
Top