Here is a VBA "solution". I have created a User Defined Function (UDF) called "PullClosedData". It takes 2 parameters:
1) a file name (no path and no extension) like: "Smith J"
2) a string in the form of a reference like: "B2:B2". This should be a single cell reference into the file noted in the first parameter. The UDF will return the contents of this cell.
Attached is an updated OverviewOfLeave.xlsm that utilizes this UDF and the previously uploaded two example employee files.
YOU WILL NEED TO CHANGE THE "Pathname" CONSTANT AT THE TOP OF THE VBA MODULE TO REFLECT THE LOCATION OF THE EMPLOYEE FILES ON YOUR SYSTEM - and, of course enable macros.
I say "solution" in quotes because in the background each use of the UDF not only opens and closes the appropriate employee file it also needs to create then close a whole new instance of Excel (without this new instance of Excel UDFs cannot open a closed workbook). As a result this is going to be very very slow.
Here's the VBA: it's rough and ready, it would need further work if you want to pursue this approach.
Finally - can't you instead consolidate your 40 employee workbooks into 40 worksheets within the Overview workbook. You could then use INDIRECT and all these problems would then go away!
Bookmarks