(C) 2009 - 2014 by Mourad Louha · All rights reserved

Create and use dynamic references to external files in Excel

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”.

Excel 2010

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.

Excel 2010

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.

 
Comments

Very nice article, but is there also a solution without opening the file? And not using macro’s?

Mourad Louha

Hi Raymond,

thanks, as far as I know, there is no solution without opening the files.

Regards

Can we do the same reference but on a hyperlinks?

Hi Stanley,

I am unsure what you are exactly meaning, but you can use the HYPERLINK function to create Hyperlinks dynamically. Example =HYPERLINK(“file:///” &”E:\Temp\Test.xlsx”) You should for a file specify its location and add the file:/// prefix.

Regards, Mourad

hi,
i like the dynamic hyperlink you have posted, but can you get the hyperlink to find a file No in different folder from the spread sheet location.

many thanks

Mourad Louha

Hi jonathan,
if you refer to my article and if I understood you request well, then, as the files (Week 01 … Week 04) should be opened, it doesn’t matter where there are.
Regards, Mourad

THANKS

Trackbacks for this post

Leave a Reply