Hyperlink only showing cell value, rather than the link working

[andrewlau2881]

I have a master sheet which has hyperlinks. I am copying these cells to smaller sheets, which are duplicated. Ideally I will update the master with the link and all of the smaller sheets will then have that link, unfortunately the links do not work in any of the smaller sheets.

I am using the formula "='MASTER'!A2" to copy that cell with the hyperlink, the text from the cell is there but the link does not work.

Is there a way to make this work?

All help hugely appreciated, as always.

Thanks

 

[myall_blues]

You can't do it that way as far as I'm aware.

Here is an alternative solution that I use. On your MASTER sheet, have two columns - one with the text you want to display in the link and the other with the link itself. Then on your smaller sheet use an =HYPERLINK formula and reference the two cells on the MASTER.

For example: MASTER sheet

Book1
	A	B
1	Text	Link
2	Mr Excel Forum	https://www.mrexcel.com/board/forums/excel-questions.10/
MASTER

Smaller sheet

Book1
	A	B
1
2	Mr Excel Forum
Sheet1

Cell Formulas
Range	Formula
A2	A2	=HYPERLINK(MASTER!B2,MASTER!A2)

 

[andrewlau2881]

You can't do it that way as far as I'm aware.

Here is an alternative solution that I use. On your MASTER sheet, have two columns - one with the text you want to display in the link and the other with the link itself. Then on your smaller sheet use an =HYPERLINK formula and reference the two cells on the MASTER.

For example: MASTER sheet

Book1
	A	B
1	Text	Link
2	Mr Excel Forum	https://www.mrexcel.com/board/forums/excel-questions.10/
MASTER

Smaller sheet

Book1
	A	B
1
2	Mr Excel Forum
Sheet1

Cell Formulas
Range	Formula
A2	A2	=HYPERLINK(MASTER!B2,MASTER!A2)

I have already done this. However, the link only works if the Master workbook is open!!!!

My workaround for that is to put the link from the master into a cell on the smaller sheet, then use the =hyperlink formula from the cell in the smaller sheet.

It seems insane that it needs to be so complicated, there must be a neater solution.