Using hyperlinks to get view a specific part of a workbook

[JaspervdH]

I have an excel file with many tables across three worksheets. I want to have a way of quickly viewing a particular table in the Excel user interface.

I'm trying to use hyperlinks to accomplish this, but I'm running into the following problem. Suppose I want to view a table that is located at Data!A150:Data!D170

If I make a hyperlink like this HYPERLINK("#Data!A150", "link"), and the view of the worksheet Data is currently above line 150, you will not see the table, as A150 will be displayed as the bottommost cell in the user interface of Excel.

To work around this, I have tried adding the hyperlink HYPERLINK("#Data!A170", "link"). This will bring the table into view, as A170 is now the bottommost cell in the user interface bringing the table into view.

However, if you scroll down below A170 in the Data worksheet and follow the same link, A170 will now be the topmost cell in the user interface, and hence the table will not be visible.

I'm really struggling with a solution to this problem. I figure I could use VBA to follow multiple hyperlinks (first A150, then A170) to accomplish what I want, but I prefer not to use VBA for this project.

I'd really like to get some help with this.

Thanks in advance.

 

[My Aswer Is This]

I suggest using a InputBox to enter the Table Name
And the script will then take you to that table

Try this:

Sub Show_Worksheet_Table()
'Modified  11/14/2022  5:44:15 AM  EST
On Error GoTo M
Dim ans As String
ans = InputBox("Enter Table Name")

Application.Goto Range(ans)
Exit Sub
M:
MsgBox "The name you entered was " & ans & vbNewLine & "There is no Table in this workbook by that name"


End Sub

 

[JaspervdH]

I suggest using a InputBox to enter the Table Name
And the script will then take you to that table

Try this:

Sub Show_Worksheet_Table()
'Modified  11/14/2022  5:44:15 AM  EST
On Error GoTo M
Dim ans As String
ans = InputBox("Enter Table Name")

Application.Goto Range(ans)
Exit Sub
M:
MsgBox "The name you entered was " & ans & vbNewLine & "There is no Table in this workbook by that name"


End Sub

Thanks. I guess no solution without VBA is possible so this will be good.

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.