Dynamic Referencing to a closed workbook

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 🙂

Mike