Dynamic Hyperlink to Sheet by Index Number

[sqevans]

Hi there! I am using INDIRECT to make it so that I can reorder my tabs and have the data on a summary page track. However, with tons and tons of tabs, I need the hyperlink to track. So, if I move the tab from position 1 to position 5, I want the hyperlink to still direct to position 1.

I have tried:

=HYPERLINK(INDIRECT("'" & sheetname(3) & "'!e11"))

This shows the proper proper data from the sheet, but the hyperlink shows a security concern.

Any ideas on how I can do this?

Thank you!


My function for sheetname is:
Function SheetName(i As Integer) As String
SheetName = Application.Caller.Worksheet.Parent.Sheets(i).Name
End Function

 

[Yongle]

Are you using Excel 2016?

The problem you are experiencing may have been caused by Microsoft update - see this link
[h=1]Hyperlinks in excel 2016 not working, giving warning meassage when try to open Hyperlinks[/h]

 

[sqevans]

HI there. Thank you for replying. I am actually on the most current version of Excel.

I'm open to another workaround.... for example... is there a way for me to simply create a hyperlink to a tab by index number? Like... on my summary page:

Cell A1 Has Link to Tab #2 (regardless of what the name of the tab is)
Cell A2 Has Link to Tab #3
Etc.

Possible?

Thank you!

 

[Yongle]

So you ARE on Excel 2016
=HYPERLINK formula appears to have been broken by the Microsoft update for some users and it sounds like you are a casualty

Before doing anything further it is essential that you determine the status of =HYPERLINK for you
- it is possible that you will discover that it does not work at all!

(Ignore INDIRECT for now) and try creating a simple hyperlink and see if the link works when you click on it

1. Try this in any cell other than E11 in any sheet
=HYPERLINK(E11,"E11")
Does the link take you to E11 in active sheet?

2. If that works (amend sheet name and) try linking to another sheet with
=HYPERLINK('Sheet Name'!E11,"test")
Does the link take you to E11 in the named sheet?

3. If that works save, close and re-open the workbook and test the same 2 links

 

[sqevans]

Hmmmm....so weird. Nope, those do not work. I am on office 365 and when I searched, the microsoft website said I was on 2019, but apparently I am wrong.

So....is this fixable or am I at the mercy of the microsoft bug gods to correct?

Thank you for your time.

 

[Yongle]

the microsoft website said I was on 2019

2019

is this fixable?

Read the link I embedded in post#2
- you may need to delve into the other replies etc
- I did spot a link to the "fix" instructions
- please update this thread with the result and (hopefully ) the solution

 

[Yongle]

In the meantime =HYPERLINK cannot be used n any workbooks

But I expect this to work for you. Please test
- right click on any cell \ Link \ Place in this document etc...
- does the link you created work?
- if that fails then you are stuck...

Assuming it works, I think it would be easiest to create the dynamic links by VBA
- are you familiar with VBA?