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

[fishep6]

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

 

[JazzSP8]

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?

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

etc.