Non working Hyperlinks when results returned from another sheet

[dave6726]

I have the following problem.
Sheet 2 has a list of names and hyperlinks to other sheets (hyperlinks work)
On sheet one I have created an” Index - Small” formula that searches sheet2 and brings back the results on sheet1
=IF(ISERROR(INDEX(Sheet2!$A$2:$B$497,SMALL(IF(Sheet2!$A$2:$A$497=Sheet2!$A$2,ROW(Sheet2!$A$2:$A$497)),ROW(2:2))-1,2)),"",INDEX(Sheet2!$A$2:$B$497,SMALL(IF(Sheet2!$A$2:$A$497=$G$9,ROW(Sheet2!$A$2:$A$497)),ROW(2:2))-1,2))

However, I also want to bring the hyperlinks results from sheet2. I have used the following.
=HYPERLINK(VLOOKUP(G12, Sheet2!$D$2:$E$10,2,0)) - (name in col D and hyperlink in col E)

My problem is that although the results show as a hyperlink on sheet1, the hyperlinks do not work from sheet1 and I am getting an error message saying “Cannot open the specified file”.

As I said the hyperlink works on sheet2 but not from sheet1 when I pull the results back.
Any advice would be greatly appreciated.

 

[dave6726]

Hi, you can try this:

=HYPERLINK("#'"&VLOOKUP(G12, Sheet2!$D$2:$E$10,2,0)&"'!A1",VLOOKUP(G12, Sheet2!$D$2:$E$10,2,0))

Thank you for your quick response

I have tried the above. I still get what looks like the hyperlink returning, however I am now receiving a "Reference is not valid" result.
Hovering now gives me: #'name'!A1 - Click once to follow. Click and hold to select this cell.

 

[FormR]

If you just put this in a spare cell:

=VLOOKUP(G12, Sheet2!$D$2:$E$10,2,0)

Does it return the name of the sheet that you want to link to? What version of Excel are you using?

 

[dave6726]

Yes it does

Excel 2010 v14.0.7190.5000 (32-Bit)

 

[FormR]

Yes it does

Then I don't understand why it doesn't work for you. Can you remove all data not relevant to the hyperlinks and upload a copy of your workbook to a file sharing site, like dropbox, and share the link here so we can take a look.

 

[dave6726]

Hope this works. Sanitised the file

https://www.dropbox.com/preview/Plans.xlsx?role=personal

 

[FormR]

Hope this works.

Hi, I don't believe it did, In dropbox you need to click on "Share" and then "Copy Link" and paste that link here.

 

[dave6726]

Hi, I don't believe it did, In dropbox you need to click on "Share" and then "Copy Link" and paste that link here.

https://www.dropbox.com/s/v0f0g4qql2dfnz5/Plans.xlsx?dl=0

 

[FormR]

Hi, you didn't implement the suggestion accurately.

See additions that you need in large red font:

=HYPERLINK("#'"&VLOOKUP(G12,Data!D2:E10,2,0)&"'!A1")

 

[dave6726]

Hi, you didn't implement the suggestion accurately.

See additions that you need in large red font:

=HYPERLINK("#'"&VLOOKUP(G12,Data!D2:E10,2,0)&"'!A1")

FormR = This is much appreciated - Thank you very much for your help. I will now endeavour to work on the rest of it. Once again Thank You

Dave

 

[dave6726]

Taking this one step further. Thanks to the advice already given, which is much appreciated, I can now get all the hyperlinks in the cells that I require.

However, the hyperlinks return in the format of: #'Fred'A2
Fred being the actual name of the worksheet. This is great, but I was wondering if there is a way of tidying it up? i.e. Just to show FRED, but for the hyperlink to still work.

I have tried various ways, but unsuccessful.

Any help would be appreciated