Hyperlinks with VLookup - Returns Error - Cannot Open Specified File

0
votes

The problem is: I have named ranges of cells in sheet3. In sheet2 I have created hyperlinks using range names so once you click in any of the links it takes you direct to the related range in sheet3.

In sheet1 I have a table in which column K is a droplist, in column L I entered this formula:

=HYPERLINK(VLOOKUP(K3,Sheet2!B48:C92,2,0))

which return the hyperlink matching to the selected value in column K the result returned is exactly the hyperlink in the sheet2 but whenever I click any of these hyperlink results an error message pop up saying: Cannot open the specified file.

Even all the links in Sheet2 is working fine and it take me direct to the linked range in Sheet3.

Do I need to insert a macro code to make the Vlookup-Hyperlinks results working and take me to the targeted range in Sheet3 and whats the that code?

1
excelvbaexcel-formula

1 Answers

1
votes

If =VLOOKUP(K3,Sheet2!B48:C92,2,0) returns the name which is the target range of the hyperlink which is working in Sheet2, then the following should work in a HYPERLINK function:

=HYPERLINK("#"&VLOOKUP(K3,Sheet2!B48:C92,2,0))

The correct syntax would be:

=HYPERLINK("[NameOfTheWorkbook]"&VLOOKUP(K3,Sheet2!B48:C92,2,0))

according to : http://office.microsoft.com/en-us/excel-help/hyperlink-function-HP010342583.aspx.

But the above syntax will also work. There the # works as a shortcut for "ThisWorkbook".

Greetings

Axel