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