Build Hyperlink from Cells in Another Workbook?

[RWeaverAMT]

I have two workbooks:

Workbook1

Workbook2

In workbook 2, I have text in cell A1. The text could be a website url, or the filepath of a file in windows, or whatever.

In workbook1, I want to build a Hyperlink formula which gets its "link_location" from cell A1 in Workbook2.

I build the following formula in, say, cell C3 of Workbook1:

=HYPERLINK('C:\Users\JohnDoe\Desktop\[Workbook2.xlsx]Sheet1'!$A$1,"Test")

The hyperlink works correctly, and opens the website/file/whatever listed in cell A1 of Workbook2... but only if Workbook2 is open. As soon as I close Workbook2, clicking the link in Workbook1 does nothing. It's still there, and does not give any error (even after F2->Enter). It just... does nothing when clicked.

Now, if I go to Workbook1, and in cell, say, E5, I enter the following formula:

='C:\Users\JohnDoe\Desktop\[Workbook2.xlsx]Sheet1'!$A$1

This populates cell E5 in Workbook1 with the text from cell A1 of Workbook2. If I then replace the formula in cell C3 of Workbook1 with the following formula:

=HYPERLINK(E5,"Test")

Then the link works, and continues to work even when Workbook2 is closed.

What gives? Is there a way to make this work without the "middle man" formula?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Build Hyperlink from Cells in Another Workbook?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.