HYPERLINK Worksheet Function
 

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

"You can create hyperlinks within a worksheet to jump from one cell to
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 ?

HYPERLINK Worksheet Function
 

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
wrote:
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

"You can create hyperlinks within a worksheet to jump from one cell to
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 ?

HYPERLINK Worksheet Function
 

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
"rng_Example"
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.........

"Pete_UK" wrote:

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
wrote:
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

"You can create hyperlinks within a worksheet to jump from one cell to
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 ?

HYPERLINK Worksheet Function
 

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

Pete

On Oct 11, 8:39 pm, David Messenger
wrote:
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
"rng_Example"
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.........



"Pete_UK" wrote:
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
wrote:
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

"You can create hyperlinks within a worksheet to jump from one cell to
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 ?- Hide quoted text -

- Show quoted text -