Dynamic Referencing to a closed workbook

Copper Contributor

Hi there,


I am trying to create a formula that will dynamically pull in a cell's value from another workbook that is closed. I want to avoid manually referring to the source workbook, and instead want to pull in a cell's value dynamically based on a changing variable, which would be the name of the source workbooks (file name) in this case. Please considering the following


  • Cell A1 contains the File Path directory of the closed workbook.
  • Cell A2 contains the Sheet Name of the closed workbook.
  • Cell A3 contains a cell reference value, lets use $C$5 for example, that I want to pull from the closed workbook.
  • Cells B2:B10 contain different File Names

Question: What formula(s) could I use, given the above information, in cell C2 in the current workbook, that would pull cell value $C$5 from the source workbook? Note that the file path, sheet name, and cell text value would remain constant, the only variable changing is the file name.


I am trying to avoid using VBA if possible and just create a cheeky formula instead but based on my research thus far, it seems like creating a macro is the only option. I know that the INDIRECT function would work if the source workbook(s) are open, but it does not work if the workbook(s) are closed, and I want the formula to work with all the workbooks being closed. I figured I would ask the community to see there is any formula functionality that could do the trick, before having to create a macro instead. 


Thanks in advance :)


1 Reply



Refresh an external data connection in Excel

Here are some untested examples, with formula and in VBA.

Although these examples are not tailored to your project, they may be of some help.

Don't think you can easily bypass VBA when it comes to closed workbooks.

I don't know of a safe way to do this without VBA, but if there is one, it's certainly more complicated than VBA.


zip codeslocation          

Examble B2 formula:



Hope I was able to help you with this info.



I know I don't know anything (Socrates)