Excel 2016 : Dynamically reference values in closed workbook?

[ausable7400]

Hi All -

I've been researching this question for a while now and all roads are leading to "not possible without VBA" but is there way to reference a closed Workbook dynamically in Excel 2016? The Stack Overflow posts are 3 to 8 years old so hopefully something has changed.

By dynamic, I want to store the path and file name in cells and then reference them in the formula

This formula works, but obviously not dynamic :

Please Login or Register  to view this content.

I will use VBA if I have to, but trying to do this via formulas instead. Any tips or links is greatly appreciated! Thank you!

[6StringJazzer]

Excel 2016? The Stack Overflow posts are 3 to 8 years old so hopefully something has changed.

Excel 2016 is also 8 years old, and nothing would have changed in that version except bug patches.

In any case, there have been no updates up through Microsoft 365 that would address this.

To dynamically reference an external file with a variable path/file name, you need the INDIRECT function. Using INDIRECT to reference another file requires the other file to be open, otherwise you get a #REF! error. VBA is the only way around it. There is an add-in library floating around called morefunc with the function INDIRECT.EXT that will do this but the implementation of that function is VBA.


Also, please update your profile to show that you are using Excel 2016. Saying "16" doesn't tell us which version you are actually using. Excel 2016, 2019, and 2021 are all identified as 16.0.