11-13-2018 09:50 AM
11-13-2018 09:50 AM
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 trying to use to hyperlink to a cell on the same tab of a spreadsheet: =HYPERLINK($U$14,"Table 20-09"). When I use the Insert Hyperlink functionality, it works fine. However, I have to use the HYPERLINK() function because it needs to work dynamically (with a passed cell reference). The sheet is not protected (at this point). Using Office 365. A solution to this problem will be greatly appreciated.
11-14-2018 05:00 AM - edited 11-14-2018 05:01 AMSolution
=HYPERLINK("#$A$3","Somewhere in the same worksheet")
=HYPERLINK("#'ANOTHER SHEET'!$A$3","Somewhere in another worksheet")
if you want a dynamic link in cell A3, put
then, the hyperlink function
=HYPERLINK($A$3, "Somewhere pointed by Cell A3")
11-15-2018 02:57 PM
01-14-2019 03:56 PM
my hyperlinks dont go to the corresponding sheet. all get errors.
i have tried different hyperlink formulas. please see my screenshots.
for example, in my first screenshot, when i select the first (blue) link at the top of the screen, i get "an unexpected error has occurred.
i get the same response when i select the (black) link under it.
the thrid image show my spreadsheet in its whole.
can you see what is causing the errors?
thanks for your help.
01-14-2019 04:44 PM
Give this formula a try for linking within the same workbook:
=HYPERLINK("#TEMPLATE!C1","Go to TEMPLATE > C1")
In terms of using other cells to piece together a hyperlink (this example uses , copy and try:
=HYPERLINK("#"&"'" & B3 & "'!" & A1, "Go To Sheet")
See how you go?
01-14-2019 04:55 PM
01-14-2019 04:59 PM
Not sure what the issue is. Try simulating in a new Workbook to see if you still have the same issue?
Check your tab doesn't have any spaces in the name as well.
Otherwise, might need to log a support ticket with Microsoft?
Hope you figure it out! Let me know if any of the above works?
01-14-2019 05:03 PM
actually, i can use your formulas on a new excel workbook, just not in my existing workbook. as you will see in the attached jpg each tab/sheet is a single name
01-14-2019 05:16 PM
I was suggesting that sometimes, accidentally, a space could be after the sheet name which isn't visible to the eye but might be sitting there as space is a character.
Anyway, the only other option I can think of to fix the issue is to recreate your sheets in a new workbook and see if that fixes the problem which is not ideal but may be worthwhile in the long run.
04-12-2019 02:44 AM
Hi, This did solve my problem with the error messages I was getting but its not dynamic and moving when rows are added in. Please, can you help?
=HYPERLINK("#Sheet1!A20", "Fuel Systems")
06-11-2019 09:59 AM
=HYPERLINK("#Sheet1!" & ADDRESS(ROW(A20),COLUMN(A20)), "Fuel Systems")
02-15-2020 02:38 AM
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.
11-10-2020 08:33 PM
@Willy Lau I tried the formulas but can't seem to get it to work in Office 365.
What I am trying to do is create a hyperlink to a changing cell in the next screen.
=INDIRECT(CONCATENATE("'Lines Parts'!",ADDRESS(1,SUM(COUNTA('Lines Parts'!$A$1:$ZZ$1)))
Currently that formula returns the value of the cell that I am trying to hyperlink to. Each month, it will need to change to the new column of data, as we register it month over month.
When I tried to hyperlink it, it says "Cannot open the specified file".