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.

 

[FormR]

Sheet 2 has a list of names and hyperlinks to other sheets (hyperlinks work)

Hi, are these hyperlinks the result of the HYPERLINK() worksheet formula or are they proper inserted hyperlinks?

 

[dave6726]

Hi, are these hyperlinks the result of the HYPERLINK() worksheet formula or are they proper inserted hyperlinks?

The HYPERLINK() worksheet formula

 

[FormR]

Can you post the formula?

 

[dave6726]

Apologises, mistook the term
The hyperlinks on sheet2 are right click, select hyperlink, select sheet and cell

 

[FormR]

Then you are going to need to use VBA of some sort - are you OK with that? Do these hyperlinks link to sheets/cells within the same workbook or to external files?

 

[dave6726]

I was hoping not to go there, but it that's the only option I will have too. The hyperlinks will always link to sheets and cell a1 within the workbook

 

[FormR]

I was hoping not to go there

We might still avoid it, does the VLOOKUP() formula return the name of the sheet that the hyperlink needs to link to? Or is there a logical way to derive the sheet from the result of that formula?

 

[dave6726]

Apologies, with holiday period and time off, I have only just got back to this

Yes it refers to the sheet =HYPERLINK(VLOOKUP(G12, Sheet2!$D$2:$E$10,2,0))
This brings back what looks like the hyperlink on my front sheet but does not link to the worksheet in the same workbook.

The original hyperlink that I have in sheet 2 does hyperlink correctly to the worksheet. But I just cant get it to hyperlink from front sheet when I pull it across from sheet 2. (hope this makes sense)
When I hover over the hyperlink on sheet 2 it gives me the correct hyperlink details
However when I hover over the returned hyperlink on front sheet it gives me "name" - Click once to follow. Click and hold to select this cell. Which then results in Cannot open the specified file.

 

[FormR]

Yes it refers to the sheet

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