Excel - HYPERLINK Worksheet Function

Asked By DavidMessenge

11-Oct-07 05:39 AM

Working in Excel 2000.

Trying to use the HYPERLINK Worksheet Function to Hyperlink from one sheet
to a Cell on another Sheet.

I can get it to work easily by using the Insert Hyperlink Menu etc, or by
VBCode, but I am deliberately trying to use this Worksheet Function (because
I want to use a Named Range (selectable by the user) and NO Macros)

Excel Help File says

another cell. For example, if the active worksheet is the sheet named June in
the workbook named Budget, the following formula creates a hyperlink to cell
E56. The link text itself is the value in cell E56.
=HYPERLINK("[Budget]June!E56", E56)"

When I try this it just keeps bringing up the message "Cannot open specified
file".

Puzzled because I can get the HYPERLINK function to jump to Web pages, other
Excel Files, Word files etc but not within the Active File itself.

Assume I am missing something incredibly obvious ?

11-Oct-07 08:01 AM

Assuming the file you are using is called Budget, then you will need
to add the .xls at the end, like this:

=HYPERLINK("[Budget.xls]June!E56", E56)"

Another way is like this:

=HYPERLINK("#June!E56","jump")

This will give you the message "jump" in the cell.

Here's something a bit more flexible if you want to jump to different
sheets and/or cells:

=HYPERLINK("#'"&A1&"'!E"&B1,"jump")

Put a sheet name in A1 (eg June) and a row number in B1 (eg 10), then
when you click "jump" it will take you to cell E10 in June sheet. Note
the apostophe after the # and before the ! - this will cater for sheet
names in A1 which have spaces in them.

Hope this helps.

Pete


On Oct 11, 10:39 am, David Messenger

11-Oct-07 03:39 PM

Thanks Pete

Tried the # and it worked fine.

The obvious mistakes I was making was I had [Budget] (like in the Help File
text )not [Budget.xls] (I left the extension off). Amazing what a bit of
sleep overnight does.

But the # is handy to know.

The detail of what I was doing was ....
For example I was using Cell A1 to have the text of named range say
Then in Cell A2 I would have something like CELL("address",INDIRECT(A1)) to
get the detailed address of this named range
Then in Cell A3 I would use HYPERLINK(A2,"GO!")

After reattempting after your advice I found this wasn't working because the
text address came back in the general format '[File Name]Sheet'!$A$1. I had
to strip out the ' from the string for it to work.

Thanks again.........

11-Oct-07 03:55 PM

You're welcome, David. Thanks for feeding back.

Pete