Problem: VLOOKUP for HYPERLINK address (in UNC format)

[James Cox]

XP Pro, Excel 2002

I need to provide a hyperlink to Access databases on other PC's in our
network - and those hyperlink addresses reside in another workbook. The
"friendly text" also needs to come from that other workbook.

My formula is

=HYPERLINK(VLOOKUP(E23,'K:\Rox\OD-2 Excel\OD2
Master.xls'!QuestionTexts,11,FALSE),VLOOKUP(E23,'K:\Rox\OD-2 Excel\OD2
Master.xls'!QuestionTexts,2,FALSE))

which shows the proper friendly text. If the VLOOKUP for the friendly text
is left out, the proper UNC reference for the .mdb shows up in the cell - in
blue and underlined, to wit:

\\otherbox\os_share\databases\OD2_Problems.mdb

However, a click on the "hyperlink" (be it the direct UNC or the friendly
text) just shows the cell formula and does not activate the .mdb.

Using the plain HYPERLINK function, with the UNC typed in - ie,

=HYPERLINK("\\otherbox\os_share\databases\OD2_Problems.mdb", "Prob db")

works, with or without the friendly text.

Any tips where I'm shooting myself in the foot? Thanks in advance for any
help!

James Cox

 

[James Cox]

OK, here's the deal.

If I do the VLOOKUPs as stand-alone cell formulas and then reference those
cells in the HYPERLINK formula - ie

Cell A1 formula is =VLOOKUP(E23,'K:\Rox\OD-2 Excel\OD2
Master.xls'!QuestionTexts,11,FALSE)

Cell A2 formula is =VLOOKUP(E23,'K:\Rox\OD-2 Excel\OD2
Master.xls'!QuestionTexts,2,FALSE))

Then use for the Cell A3 formula ==HYPERLINK(A1,A2)

the hyperlink works even if the OD2 Master.xls workbook is closed.

Interestingly enough, the old formula in the original note below also works
if the OD2 Master.xls workbook is open.

Most puzzling! Does anyone have any insights into why a "first level" cell
formula (the VLOOKUPs in their own cells) apparently can access values in an
unopened .xls while a "second level" VLOOKUP (the ones that are part of the
original HYPERLINK formula) cannot? Is this behavior common for other
worksheet functions?

James Cox