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 find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Try removing the '$'s
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
=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.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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 ;)
 

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
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?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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. :)
 
Solution

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
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. :)
That's so cool @johnnyL . It worked!!!!!! Thanks a lot! You're a life saver. :)
 

Forum statistics

Threads
1,141,070
Messages
5,704,112
Members
421,327
Latest member
Msh

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
Top