Forum Discussion

Jan Grobbelaar's avatar
Jan Grobbelaar
Copper Contributor
Nov 13, 2018
Solved

HYPERLINK function returns "Cannot open specified file"

I am trying to use the HYPERLINK function but to no avail. I have exhausted all the standard help available on this, but I cannot figure out what is wrong, Here is an example of the syntax I am tryin...
excel
Formulas and Functions
office 365
  • Willy Lau's avatar
    Willy Lau
    Nov 14, 2018
    =HYPERLINK("#$A$3","Somewhere in the same worksheet")

    or

    =HYPERLINK("#'ANOTHER SHEET'!$A$3","Somewhere in another worksheet")

    if you want a dynamic link in cell A3, put

    #A5

    then, the hyperlink function

    =HYPERLINK($A$3, "Somewhere pointed by Cell A3")
Wayne2020's avatar
Wayne2020
Copper Contributor
Feb 15, 2020

Hi Jan Grobbelaar ,  I know this thread is a bit old, but I just spent some time working out how to make the Hyperlink formula fully dynamic -- so that Users can rename worksheets without breaking the links -- and thought you may be interested.  This formula to access "#'Change Log'!$A$1" works as I expect:

 

=HYPERLINK(CHAR(35) & CHAR(39) & REPLACE(CELL("filename", 'Change Log'!$A$1), 1, FIND("]", CELL("filename", 'Change Log'!$A$1)), "") & CHAR(39) & CHAR(33) & "$A$1", "Go to Change Log")

 

I would be interested if anyone can simplify this and still retain the dynamic nature.

 

Cheers, Wayne

Resources