Hyperlinks to named ranges using Name Manager for index/menu

Marty Plante

Marty Plante

New Member
Joined
Dec 28, 2016
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I have searched for add-in's, various terminology, and I'm coming up with very little in regards to my question.

I have a workbook with multiple pages (over 40) with 20 "sectors" in each. A total of 800 named ranges. In order to create the index or menu in a matrix style where you click on a link to go to range 8-15, for example, I have to insert each hyperlink individually on the index page.

Is there a method in which I can take the Name Manager and access the in-bound (?) not sure on terminology, and add a hyperlink to each named range? Ideally I would love to see the Name Manager show a column for inbound links and a column to put the name of the link, then all hyperlinks could be created without working from the menu/links page.

I have thought about VBA for this, I'm not an expert on VBA code, but can manage basic steps. But to run a code would require looking at any named ranges already used and going to the next on the list. All sounds quite complicated, and risky.

Thoughts?
 
Sort by date Sort by votes
You can use INDIRECT function to achieve what you want. The following link directs you to a sample file with a 10x10 matrix with dynamic hyperlinks and a 10x10 matrix with 100 ranges in plain text (eg. "A1").

Dynamic Hyperlinks.xlsx

The key for the hyperlinks to work is a named range called Link

=INDIRECT(INDEX(Ranges,ROW()-ROW(Corner),COLUMN()-COLUMN(Corner)))
[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Named Range[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Link[/TH]
[TD]=INDIRECT(INDEX(Ranges,ROW()-ROW(Corner),COLUMN()-COLUMN(Corner)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Corner is a named range at the top right corner of the matrix containing the hyperlinks. It is used within the ROW()-ROW(Corner) and COLUMN()-COLUMN(Corner) functions to get the relative coordinates of each cell in the matrix.

Ranges is a 10x10 named range where you can find text references for each of the named ranges (Sectors in your example).

INDEX will reference the value in Ranges corresponding to the current row and column.

INDIRECT transforms the contents of the referenced cell (the one returned by INDEX) into a valid reference. This is where the magic happens, making a static text into a reference than can be the argument for the HYPERLINK function.


[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD]=HYPERLINK("#Link",O3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Insert this formula to every cell in the Hyperlink matrix. The HYPERLINK formula will use the named range Link to dynamically refer to the named ranges in the text reference matrix. The second argument makes your cell display the destination of your Hyperlink.

Hope it helps. Regards,
Franz

PD. My sample file looks something like this:


[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[TH]F
[/TH]
[TH]G
[/TH]
[TH]H
[/TH]
[TH]I
[/TH]
[TH]J
[/TH]
[TH]K
[/TH]
[TH]L
[/TH]
[TH]M
[/TH]
[TH]N
[/TH]
[TH]O
[/TH]
[TH]P
[/TH]
[TH]Q
[/TH]
[TH]R
[/TH]
[TH]S
[/TH]
[TH]T
[/TH]
[TH]U
[/TH]
[TH]V
[/TH]
[TH]W
[/TH]
[TH]X
[/TH]
[/TR]
[TR]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Sheets​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Sheets​
[/TD]
[TD][/TD]
[TD]
Test1​
[/TD]
[TD][/TD]
[TD]
Test2​
[/TD]
[TD][/TD]
[TD]
Test3​
[/TD]
[TD][/TD]
[TD]
Test4​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD][/TD]
[TD]
Corner​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[TD]
6​
[/TD]
[TD]
7​
[/TD]
[TD]
8​
[/TD]
[TD]
9​
[/TD]
[TD]
10​
[/TD]
[TD][/TD]
[TD]
Sector​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[TD]
6​
[/TD]
[TD]
7​
[/TD]
[TD]
8​
[/TD]
[TD]
9​
[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD]
Sectors​
[/TD]
[TD]
1​
[/TD]
[TD]
Test1​
[/TD]
[TD]
D15​
[/TD]
[TD]
E15​
[/TD]
[TD]
F15​
[/TD]
[TD]
G15​
[/TD]
[TD]
H15​
[/TD]
[TD]
I15​
[/TD]
[TD]
J15​
[/TD]
[TD]
K15​
[/TD]
[TD]
L15​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
Test1​
[/TD]
[TD]
D15​
[/TD]
[TD]
E15​
[/TD]
[TD]
F15​
[/TD]
[TD]
G15​
[/TD]
[TD]
H15​
[/TD]
[TD]
I15​
[/TD]
[TD]
J15​
[/TD]
[TD]
K15​
[/TD]
[TD]
L15​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
C16​
[/TD]
[TD]
D16​
[/TD]
[TD]
E16​
[/TD]
[TD]
F16​
[/TD]
[TD]
G16​
[/TD]
[TD]
H16​
[/TD]
[TD]
I16​
[/TD]
[TD]
J16​
[/TD]
[TD]
K16​
[/TD]
[TD]
L16​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
C16​
[/TD]
[TD]
D16​
[/TD]
[TD]
E16​
[/TD]
[TD]
F16​
[/TD]
[TD]
G16​
[/TD]
[TD]
H16​
[/TD]
[TD]
I16​
[/TD]
[TD]
J16​
[/TD]
[TD]
K16​
[/TD]
[TD]
L16​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
C17​
[/TD]
[TD]
D17​
[/TD]
[TD]
E17​
[/TD]
[TD]
F17​
[/TD]
[TD]
G17​
[/TD]
[TD]
H17​
[/TD]
[TD]
I17​
[/TD]
[TD]
J17​
[/TD]
[TD]
K17​
[/TD]
[TD]
L17​
[/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
C17​
[/TD]
[TD]
D17​
[/TD]
[TD]
E17​
[/TD]
[TD]
F17​
[/TD]
[TD]
G17​
[/TD]
[TD]
H17​
[/TD]
[TD]
I17​
[/TD]
[TD]
J17​
[/TD]
[TD]
K17​
[/TD]
[TD]
L17​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
C18​
[/TD]
[TD]
D18​
[/TD]
[TD]
Test2​
[/TD]
[TD]
F18​
[/TD]
[TD]
G18​
[/TD]
[TD]
H18​
[/TD]
[TD]
I18​
[/TD]
[TD]
J18​
[/TD]
[TD]
K18​
[/TD]
[TD]
L18​
[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
C18​
[/TD]
[TD]
D18​
[/TD]
[TD]
Test2​
[/TD]
[TD]
F18​
[/TD]
[TD]
G18​
[/TD]
[TD]
H18​
[/TD]
[TD]
I18​
[/TD]
[TD]
J18​
[/TD]
[TD]
K18​
[/TD]
[TD]
L18​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
7​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
C19​
[/TD]
[TD]
D19​
[/TD]
[TD]
E19​
[/TD]
[TD]
F19​
[/TD]
[TD]
G19​
[/TD]
[TD]
H19​
[/TD]
[TD]
I19​
[/TD]
[TD]
J19​
[/TD]
[TD]
K19​
[/TD]
[TD]
L19​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
C19​
[/TD]
[TD]
D19​
[/TD]
[TD]
E19​
[/TD]
[TD]
F19​
[/TD]
[TD]
G19​
[/TD]
[TD]
H19​
[/TD]
[TD]
I19​
[/TD]
[TD]
J19​
[/TD]
[TD]
K19​
[/TD]
[TD]
L19​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
8​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
C20​
[/TD]
[TD]
D20​
[/TD]
[TD]
E20​
[/TD]
[TD]
F20​
[/TD]
[TD]
G20​
[/TD]
[TD]
H20​
[/TD]
[TD]
I20​
[/TD]
[TD]
J20​
[/TD]
[TD]
K20​
[/TD]
[TD]
L20​
[/TD]
[TD][/TD]
[TD]
6​
[/TD]
[TD]
C20​
[/TD]
[TD]
D20​
[/TD]
[TD]
E20​
[/TD]
[TD]
F20​
[/TD]
[TD]
G20​
[/TD]
[TD]
H20​
[/TD]
[TD]
I20​
[/TD]
[TD]
J20​
[/TD]
[TD]
K20​
[/TD]
[TD]
L20​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
9​
[/TD]
[TD][/TD]
[TD]
7​
[/TD]
[TD]
C21​
[/TD]
[TD]
D21​
[/TD]
[TD]
E21​
[/TD]
[TD]
F21​
[/TD]
[TD]
G21​
[/TD]
[TD]
H21​
[/TD]
[TD]
I21​
[/TD]
[TD]
J21​
[/TD]
[TD]
K21​
[/TD]
[TD]
L21​
[/TD]
[TD][/TD]
[TD]
7​
[/TD]
[TD]
C21​
[/TD]
[TD]
D21​
[/TD]
[TD]
E21​
[/TD]
[TD]
F21​
[/TD]
[TD]
G21​
[/TD]
[TD]
H21​
[/TD]
[TD]
I21​
[/TD]
[TD]
J21​
[/TD]
[TD]
K21​
[/TD]
[TD]
L21​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
10​
[/TD]
[TD][/TD]
[TD]
8​
[/TD]
[TD]
C22​
[/TD]
[TD]
D22​
[/TD]
[TD]
E22​
[/TD]
[TD]
F22​
[/TD]
[TD]
G22​
[/TD]
[TD]
H22​
[/TD]
[TD]
I22​
[/TD]
[TD]
J22​
[/TD]
[TD]
K22​
[/TD]
[TD]
L22​
[/TD]
[TD][/TD]
[TD]
8​
[/TD]
[TD]
C22​
[/TD]
[TD]
D22​
[/TD]
[TD]
E22​
[/TD]
[TD]
F22​
[/TD]
[TD]
G22​
[/TD]
[TD]
H22​
[/TD]
[TD]
I22​
[/TD]
[TD]
J22​
[/TD]
[TD]
K22​
[/TD]
[TD]
L22​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
11​
[/TD]
[TD][/TD]
[TD]
9​
[/TD]
[TD]
C23​
[/TD]
[TD]
D23​
[/TD]
[TD]
E23​
[/TD]
[TD]
F23​
[/TD]
[TD]
G23​
[/TD]
[TD]
H23​
[/TD]
[TD]
Test3​
[/TD]
[TD]
Test4​
[/TD]
[TD]
K23​
[/TD]
[TD]
L23​
[/TD]
[TD][/TD]
[TD]
9​
[/TD]
[TD]
C23​
[/TD]
[TD]
D23​
[/TD]
[TD]
E23​
[/TD]
[TD]
F23​
[/TD]
[TD]
G23​
[/TD]
[TD]
H23​
[/TD]
[TD]
Test3​
[/TD]
[TD]
Test4​
[/TD]
[TD]
K23​
[/TD]
[TD]
L23​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]
12​
[/TD]
[TD][/TD]
[TD]
10​
[/TD]
[TD]
C24​
[/TD]
[TD]
D24​
[/TD]
[TD]
E24​
[/TD]
[TD]
F24​
[/TD]
[TD]
G24​
[/TD]
[TD]
H24​
[/TD]
[TD]
I24​
[/TD]
[TD]
J24​
[/TD]
[TD]
K24​
[/TD]
[TD]
L24​
[/TD]
[TD][/TD]
[TD]
10​
[/TD]
[TD]
C24​
[/TD]
[TD]
D24​
[/TD]
[TD]
E24​
[/TD]
[TD]
F24​
[/TD]
[TD]
G24​
[/TD]
[TD]
H24​
[/TD]
[TD]
I24​
[/TD]
[TD]
J24​
[/TD]
[TD]
K24​
[/TD]
[TD]
L24​
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Workbook Index of Sheet name Hyperlinks + additional information
Replies
1
Views
394
stuartgb100
S
M
  • Solved
Extract list of all named ranges in a worksheet
Replies
1
Views
135
saboh12617
S
R
  • Solved
#NAME Error in Image hyberlink
2
Replies
16
Views
389
Ramadan
R
I
  • Question Question
Vba has stopped adding hyperlink to my customers name
Replies
3
Views
144
ipbr21054
I
M
  • Question Question
Hyperlink Formula
Replies
2
Views
1K
Mplz
M

Share this page

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
Back
Top