Trying to use Pull function with VLOOKUP function

Background: I'm working on a project that will be distributed throughout the company. As such, linked files in formulas may need to be changed periodically (I'm trying to idiot proof this project). I had originally just intended to have the users simply use the Change Source however this appears to crash Excel every time I try it.

Instead I've opted to put the filename in cell A1 of a database and have that value changed through a macro. The problem I am having however is that I had been using a VLOOKUP that looked like this:

=VLOOKUP([@[e-mail]],'C:\folders\[filename.xlsx]Sheet Name'!$B:$R,8,FALSE)

To cross reference each e-mail in the project with information tied to that e-mail in a file they have downloaded from the intranet.

I tried using something like this:

=VLOOKUP([@[e-mail]],Indirect($A$1&"!$B:$R"),8,FALSE)

However the file seems to need to be opened for that to work.

Then I discovered the Pull function however using the pull function as such does not help at all:

=VLOOKUP([@[e-mail]],PULL($A$1&"!$B:$R"),8,FALSE)

I suspect that this is not the type of use the Pull Function was intended for. If anyone could help figuring out how to reference a closed file in a Vlookup indirectly it would be appreciated.

I've read about Indirect.EXT however I'm concerned that I would need to make all users download and install that as well which I want to save as a last last resort.

Thank You.

Was informed that that solution probably won't fly with IT.