Renaming a hyperlink on a different sheet to come through as a Vlookup

fishep6

New Member
Joined
Feb 10, 2014
Messages
43
I have a series of hyperlinks that I want to rename on a hidden sheet. I have renamed these by using the edit hyperlink function.

My problem is that I have a formula on a separate sheet that does a Hlookup on the sheet with the hyperlinks in to bring back the correct link. Before I rename the hyperlink on the other sheet, the formula works perfectly but brings through a long untidy hyperlink. When I rename the Hyperlink however the formula brings through the renamed hyperlink text, but the link actually doesn't work when you click on it.

the formula is below

=IF($E$35="A",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$C$3:$C$80,56,FALSE)),
IF($E$35="B",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$D$3:$D$80,56,FALSE)),
IF($E$35="C",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$E$3:$E$80,56,FALSE)),
IF($E$35="D",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$F$3:$F$80,56,FALSE)),
IF($E$35="E",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$G$3:$G$80,56,FALSE)),
IF($E$35="F",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$H$3:$H$80,56,FALSE)),
IF($E$35="G",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$I$3:$I$80,56,FALSE)),
(IF($E$35="H",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$J$3:$J$80,56,FALSE)),
(IF($E$35="I",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$K$3:$K$80,56,FALSE)),
(IF($E$35="J",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$L$3:$L$80,56,FALSE)),
(IF($E$35="K",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$M$3:$M$80,56,FALSE)),
"clear")))))))))))))))
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Seems to work OK for me :-/

What does the "Useful Data" look like?

What does the "long untidy hyperlink" look like?

Have you tried setting the second Hyperlink property?

Code:
=IF($E$35="A",HYPERLINK(HLOOKUP($H$35,'Useful Data'!$C$3:$C$80,56,FALSE),"Click Here"),
etc.

:)
 
Upvote 0

Forum statistics

Threads
1,223,641
Messages
6,173,506
Members
452,518
Latest member
SoerenB

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