Someone asked in my favorite German forum about Microsoft Office if it is possible to dynamically build a reference to an Excel range located in another file. The following solution addresses a range in an external file by using the Excel functions OFFSET(), INDIRECT() and ADDRESS().
Let’s suppose, we have some files which all have an identical structure for the data. The screenshot below shows 4 files, each one providing fictive data for the days of one week. The values are stored in each file in the cells C4 to C10 of a sheet named “Data”.