Hyperlinks to named ranges using Name Manager for index/menu

[Marty Plante]

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?

 

[FranzV]

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)))

Named Range Formula Link =INDIRECT(INDEX(Ranges,ROW()-ROW(Corner),COLUMN()-COLUMN(Corner))) <tbody> </tbody>

<tbody>
</tbody>

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.

Cell Formula C3 =HYPERLINK("#Link",O3) <tbody> </tbody>

<tbody>
</tbody>

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:

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

<tbody>
</tbody>