Copying Dynamic Hyperlinks

[Thomas Scott]

I have searched the web and several Excel forums trying to find a solution (formula or macro) for easily copying a formula or running a macro that can use header row labels to point to the matching worksheet name with also a return to the starting point when done. All of this is done in one workbook. I did find a nice macro that creates all the worksheets from the Data sheet header row.

1. What is needed is a way to copy dynamic hyperlinks across an entire row in the Data sheet with each one pointing to A1 in the sheet matching the header row name. Most sheet names will have text, a space, and sometimes an underscore. A formula would be nice but a macro is probably inevitable (I am not a macro programmer).

2. The Data sheet (typically with hundreds of rows and columns) is where numeric information is entered while the hyperlink allows entry of comments and text that is not quantitatively analyzed and takes up too much space in the Data sheet.

3. There should also be a return hyperlink back to the Data sheet in A2 of all the the worksheets. Yes, F5 return gets you back but some students will want to click on a return (clickers vs keyboardists).

Any assistance would be appreciated.

 

[RasGhul]

You could use =HYPERLINK("#"&B1&"!A1",B1) for your "Link to" formula and =HYPERLINK("#"&"Data!A1","Data") for the return.

Its also possible to lookup within the hyperlink so eg the link for Variable1 - Alpha sheet can you link you directly to that sheet & cell.

 

[Toadstool]

Hi Thomas Scott,

Does this work for you?

Thomas Scott.xlsx
	A	B	C	D	E
1		Alpha	Beta	_Toad	Big - Sheet
2	Documents	Alpha	Beta	_Toad	Big - Sheet
3	Variable1
4	Variable2
5	Variable3
6	Variable4
7	Variable5
Data

Cell Formulas
Range	Formula
B2:E2	B2	=IF(B$1="","",HYPERLINK("#'"&B$1&"'!$A$1",B$1))

Thomas Scott.xlsx
	A	B	C
1		Citations
2	Return
3
Alpha

Cell Formulas
Range	Formula
A2	A2	=HYPERLINK("#Data!$A$1","Return")

 

[Thomas Scott]

Hi Thomas Scott,

Does this work for you?

Thomas Scott.xlsx
	A	B	C	D	E
1		Alpha	Beta	_Toad	Big - Sheet
2	Documents	Alpha	Beta	_Toad	Big - Sheet
3	Variable1
4	Variable2
5	Variable3
6	Variable4
7	Variable5
Data

Cell Formulas
Range	Formula
B2:E2	B2	=IF(B$1="","",HYPERLINK("#'"&B$1&"'!$A$1",B$1))

Thomas Scott.xlsx
	A	B	C
1		Citations
2	Return
3
Alpha

Cell Formulas
Range	Formula
A2	A2	=HYPERLINK("#Data!$A$1","Return")

Brilliant! Works perfectly and more important I understand why. It was a simple IF statement that went either way depending!

 

[Toadstool]

Glad to hear it.