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:
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.
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?
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.