Thread: Reference filename in formula

I'm working with multiple Excel files and need to figure out how to reference a filename from one file in the formula of a cell in another file.

First, let me explain conceptually what I'm trying to do. I have data from several years in a file; each year's data is in a separate worksheet in the data file. I have an output file where I want to place the data. The data structure and output structure for each year will be exactly the same. However, from time to time, the data file name and worksheet names may change. I want to be able to type the name of the operative data file in my output file and create formulas in the output file that reference the cells in the data file based on the data file and worksheet names that I specify in the output file.

Now I'll illustrate by "simple" (probably not) example:

File 1 is called "Data.xls" and has worksheet "2006 Data"
File 2 is called "Output.xls" and has worksheet "Report"

Let's say I want to capture the information in '[Data.xls]2006 Data'!$A$1 and put it into '[Output.xls]Report'!$B$5.

I know I can just pull the data by typing "=" in my Output.xls cell and then going directly to the Data.xls cell, creating a direct link. However, suppose I want to pull 2005 data instead of 2006 data, e.g., I want to reference '[Data.xls]2005 Data'!$A$1 instead of '[Data.xls]2006 Data'!$A$1.

I want to be able to type the name of the Excel file and / or worksheet where I'm drawing data from as fields in my destination file, and then use those as references in my formulas. I can figure out how to create a line of text in my Output.xls file that replicates the name of my data file location (e.g., 'Data.xls' 2005 Data or 'Data.xls' 2006 Data), but I can't figure out how to have this name read as part of a formula in my Output.xls file.

Let's say the cell '[Output.xls]Report'!$a$3 has the following text entry: [Data.xls]2006 Data. How can I create a formula in '[Output.xls]Report'!$B$5 (my destination field) that references this text and turns it into a file reference rather than having a direct link to an original data file.

In other words, '[Output.xls]Report'!$B$5 should reference '[Output.xls]Report'!$a$3 plus the cell reference $a$1 from the operative data file, convert all of this to a formula and thus pull the data from '[Data.xls]2005 Data'!$A$1 or whatever data file and worksheet I specify as a reference.

I hope this is not too confusing. Would really appreciate any help, or any better ways to do this.

You need to use the INDIRECT function. This allows you to convert a text string to a cell reference, i.e. in B5

=INDIRECT("'"&A3&"'!$A$1")

Note: INDIRECT doesn't work with closed workbooks