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”.
Now, let’s also suppose that we have a report file which should extract the weekday values of a previously mentioned file and display the sum of the values. Usually, following formula can be used:
=SUM([Week.02.xlsx]Data!$C$3:$C$9)
However, if we would like to change the source file, for example use “Week.03.xlsx” instead of “Week.02.xlsx”, we have to manually adjust the formula.
In order to dynamically create a reference to the desired range, I firstly defined some cells storing configuration values. These are in detail the week number in D3, the name of the source sheet in D5, the start and end row from the source sheet in D6 and D7 and the used column in the source sheet in D8. The filename can be then calculated by using the formula:
="Week"&"."&TEXT($D$3;"00")&"."&"xlsx"
The Excel function OFFSET() returns a reference to a range that is moved by “x” rows and “y” columns and has a height of “h” cells and a width of “w” cells, relative to a specified cell or range in the first argument of the function. So, if we would like to get the range from C4 to C10, we may use following arguments in the function:
=OFFSET($C$4; 0; 0; 7; 1) where x = 0, y = 0, h = 7 and w = 1
Please note, that we will later replace the static value for the height by the formula “End row – Start row + 1”. The function ADDRESS() allows to dynamically calculate the address of a range by specifying a row number, a column number and the source sheet name. As our source sheet is located in another file, the source filename may be prepended to the sheet name and, of course, a correct Excel syntax ensured.
= ADDRESS($D$6;$D$8;;;"["&$D$4&"]"&$D$5)
The OFFSET() functions expects a reference in its first argument, so we should encapsulate the result returned by the ADDRESS() function into the INDIRECT() function, which returns a range from a string. Finally, we may encapsulate the current formula into the SUM() function for gettings the sum of the values contained in C4:C10.
=SUM(OFFSET(INDIRECT(ADDRESS($D$6;$D$8;;;"["&$D$4&"]"&$D$5));0;0;$D$7-$D$6+1;1))
That’s all; the configuration value in D3 then easily permits to choose the week. However, for each chosen value, the corresponding file may be opened. Otherwise the formula will return the error value #REF!. Of course, the formula can be extended by an IF-Statement and, in case of an error, display a more user friendly message. The downloadable zip archive contains my sample files in the XLSX format.
This article has also been published on the German Version of the Excel Ticker blog.
THANKS