Hyperlink Formula Query

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
Hi all,

Could you please let me know what's wrong with the formula below. I am using the HYPERLINK function together with some helper columns to complete a link to another worksheet (with a name with space and a dash symbol), but I end up getting an invalid reference error. Thanks in advance!

ABCD
1Name of the worksheetCell Reference (specific cell in the worksheet I want to be selected when the link on the last column is clicked)Friendly Name (part of the HYPERLINK function)HYPERLINK formula
2PS - X1Q10Item 1
=HYPERLINK("#"&$A2&"!"&$B2,$C2)​


Regards,
Zed
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
=HYPERLINK('PS - X1'!Q10,C2) ' Works

The closest I can get without throwing an error is:
=HYPERLINK("'" & A2 & "'!" & B2,C2)
but it doesn't appear to be referencing any cell though :( Maybe you, or someone else can take it from there and cross the goal line.
 
Upvote 0
Upon further review ... =HYPERLINK('PS - X1'!Q10,C2) only shows the text that is in the PS - X1'!Q10 when the mouse hovers over the cell with the hyperlink, it does not jump to there, it throws an error when the hyper link is clicked on. :(

I could not get the jump to work at all with a space and/or dash in the sheet name.

I had to use =HYPERLINK("#PSX1!Q10",C2) to get the jump to work.

So the formula I came up with, that works, that uses the helper cell references is pretty much the same formula in the OP's first post :(
=HYPERLINK("#" & A2 & "!" & B2,C2) ' This assumes no spaces or dashes in the worksheet name in A2 ;)
 
Upvote 0
Upon further review ... =HYPERLINK('PS - X1'!Q10,C2) only shows the text that is in the PS - X1'!Q10 when the mouse hovers over the cell with the hyperlink, it does not jump to there, it throws an error when the hyper link is clicked on. :(

I could not get the jump to work at all with a space and/or dash in the sheet name.

I had to use =HYPERLINK("#PSX1!Q10",C2) to get the jump to work.

So the formula I came up with, that works, that uses the helper cell references is pretty much the same formula in the OP's first post :(
=HYPERLINK("#" & A2 & "!" & B2,C2) ' This assumes no spaces or dashes in the worksheet name in A2 ;)
Thanks for looking into this. I wonder if this thread could help with the symbols: Issue with hyperlink formula when a space character is in the destination worksheet name. I was looking into it but couldn't make sense of some parts of the formula. Could you have a look?
 
Upvote 0
BINGO @Zedrick13 !!!

=HYPERLINK("#'" & A2 & "'!" & B2,C2) ' Should be your path to enlightenment LOL

The single quote after the '#' and before the '!' fixed the problem that Sheet names with spaces and/or dashes present. :)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top