Hyperlink function only fully works when referring workbook is open

[batchmaster]

I have an excel workbook (workbook 1) which I use to create recipes/formulas. I type in the code for a raw material and the columns in that row automatically populate with miscellaneous information (name, density, etc.), by referring to information in another workbook (workbook 2).



What I would I would like to do is have that name column automatically populate in workbook 1 with a hyperlink that refers to the correct pdf on our network for each raw material.

so the hyperlink function in excel is formatted =hyperlink (address, friendly name). I use the vlookup function within the hyperlink to pull up the friendly name I want and the pdf address, which are stored in workbook 2. On worksheet 1 the friendly name always appears correctly, even when workbook 2 is closed, but the hyperlink only works when workbook 2 is open. nothing happens (even a message box warning) if workbook 2 is closed. I would prefer workbook 2 to be closed. Is there anyway to do this? any suggestions? I'm open to vba and macros if needed, but I am just learning about them

Things I've tried/noticed:
1. manually adding the hyperlink address. Did this as more of a troubleshooting to see where the problem was. same issue. Using the vlookup that refers to another workbook within "friendly name" portion appears to interfere with pulling up the pdf address? oddly enough it still appears with the correct name


2. looking up the address and friendly name separately from workbook 2 and placing them in separate columns in workbook 1. I then create a hyperlink by referring to those cells. It works. workbook 2 does not need to be open. everything updates accurately when the code for the raw material is changed, and it is directed towards the correct pdf. However I would prefer to not clutter up worksheet one with additional columns.

[ArviLaanemets]

Why such complicacy?

In a single excel workbook, on one sheet create a table of all materials - with material as leftmost column, and other parameters for material in same row.

In another sheet, you have formula(s) table, where one column is for material. You enter there a material (in same format as in leftmost column of materials table, and all parameters for this material are read into next columns using Excel VLOOKUP() formulas.