Excel Hyperlink To Another Hyperlink On Another Sheet

Question

Based upon a value in cell E138 on Sheet 1, I am trying to look up that value in a table on Sheet 2 and open the corresponding Hyperlink in the 7th column over which is a link to Sheet 3, Sheet 4 etc.

If the Hyperlink on Sheet 2 is a web address, it works great with this code:

=IF(E138="","",HYPERLINK(VLOOKUP(E138,'Sheet 2'!C5:I10,7,0),"CLICK HERE FOR DATASHEET"))

But if I change the Hyperlinks on Sheet 2 from a web address to go to a different Sheet (ie Sheet 3) it does not work. With this code I get a Reference Isn't Valid Error.

=IF(E138="","",HYPERLINK("#(VLOOKUP(E138,'Sheet 2'!C5:I10,7,0))","CLICK HERE FOR DATASHEET"))

This gives me a Cannot Open Specified File error:

=HYPERLINK((VLOOKUP(E138,'Sheet 2'!C5:I10,7,0)), "CLICK HERE FOR DATASHEET")

Sheet 3, Sheet 4 etc contains separate datasheets. Cell E138 on Sheet 1 contains the datasheet part number that I am looking up on Sheet 2.

Please help.

Answer 1

First of all,

=IF(E138="","",HYPERLINK("#(VLOOKUP(E138,'Sheet 2'!C5:I10,7,0))","CLICK HERE FOR DATASHEET")) 

would need to be

=IF(E138 = "","",HYPERLINK("#" & VLOOKUP(E138,'Sheet 2'!C5:I10,7,0),"CLICK HERE FOR DATASHEET"))`

Secondly,

=HYPERLINK((VLOOKUP(E138,'Sheet 2'!C5:I10,7,0)), "CLICK HERE FOR DATASHEET")

should be

=HYPERLINK("#" & VLOOKUP(E138,'Sheet 2'!C5:I10,7,0), "CLICK HERE FOR DATASHEET")

Third, is your sheet really Sheet 2 (with a space), or is it the defaut Sheet2

Answer 2

I solved the problem without having to change the original formula:

=IF(E138="","",HYPERLINK(VLOOKUP(E138,'Sheet 2'!C5:I10,7,0),"CLICK HERE FOR DATASHEET"))

On Sheet 2; which has the Table being used to look up the Hyperlink, you have to Edit the Hyperlink (right click>edit hyperlink) and add a # sign in front of the default text that is displayed in the "Text To Display" box.

For example: #'CBO68HP51-100A6'!A1

CBO68HP51-100A6 is the name of my sheet that I am linking to.