1

I have the following information in Excel:

 Part Number           Part Name
199-102-002-B    Miniature Angle Mount Riser Block
199-102-092-C    Pinned Beamsplitter Mount
199-102-011-C    Camera Sleeve
199-102-032-D    1mm Aperture
199-102-081-D    VIS Attachment Plate

The part numbers can be used to look up the file location of the part, and I've created a function (MyPath()) that returns the path to the part's folder when given the part number. I would like to enable the user to simply click on the text in the second column and have the folder for the part open.

Using the formula below, I can get the link to work properly, but the text is just the part number, not the part name:

=HYPERLINK(MyPath(A2),A2)

Using this formula, the part name shows up properly, and when I hover over the cell, my cursor changes to a hand like there is a hyperlink there, but clicking on it doesn't do anything.

=HYPERLINK(MyPath(A2),VLOOKUP(A2,PartsArray,2,FALSE))

Here are the other variations that I have tested that have worked:

=HYPERLINK("C:\The\Path\To\The\Part\Folder","Miniature Angle Mount Riser Block")
=HYPERLINK(MyPath(A2),"Miniature Angle Mount Riser Block")
=HYPERLINK("C:\The\Path\To\The\Part\Folder",VLOOKUP(A2,PartsArray,2,FALSE))
=HYPERLINK(MyPath(A2),C2) <--- Where "C2" contains the VLOOKUP function

Only when I use both my custom function and the VLOOKUP() function together does the hyperlink stop working.

At this point, the closest thing I've found to a solution is what I did in the last variation shown above (i.e. moving the VLOOKUP portion of the formula to another column and then hiding that column). But there must be some explanation for this. Can anyone explain to me what's going on?

Edit:

The VLOOKUP function is referencing an array on a separate sheet that includes all the parts that will be used. The intention with this sheet is to enable someone in the assembly area to enter a part number and have the name of the part be shown in the adjacent cell. I suppose I could recreate the VLOOKUP function by using the MATCH function (in combination with a few others), but I'd rather identify the real problem and fix that than blindly search for solutions that very likely won't fix anything.

0

Evidently, the HYPERLINK function really doesn't like references to external workbooks that aren't also open (see this post for another example of when the function breaks). The best solution I can come up with is therefore to move the references to external workbooks to another cell in the workbook. The only other option that I know of would be to create a macro that creates the hyperlink on the desired cell itself. But that's not very convenient if you want to have many hyperlinks in a sheet, or if you don't know ahead of time where the text that needs to be hyperlinked is going to be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.