The attached is to establish that the concept works at your end.

There's a short macro called blah which writes formulae to cells E22:P23 on the c.Ext(Rev) sheet (shaded).
Currently the formulae have not been changed.
Open the attached and the source file and before you run anything, make sure you can see values in the shaded range (perhaps copy and paste-values as they stand to another sheet or workbook for comparison later).
Now run blah.
(If it comes up with the File Open dialogue box there's something wrong with filenames.)
Revisit the shaded area. Is it giving the same results?

If so, you could close the source workbook now and the formulae should change to include path info and they should still show values.
Final test: save, close and reopen the attached (all with the source workbook firmly closed) - can you still see values in the shaded range?

The code in blah needs to be streamlind and made more robust and be able to cater for multiple sheets and different sized ranges as well as handle paths so that the source workbook never needs to be opened.