formula referencing other workbooks

Question

Given a list of workbook's filenames, write a formula to reference a specific cell value in the specified workbook.

A----------B-------C-----D--------------------------------E----------

workbook1  sheet1  A1    '[workbook1.xlsx]sheet1'!$A$1    =(????
workbook2  sheet1  A1    '[workbook2.xlsx]sheet1'!$A$1
workbook3  sheet1  A1    '[workbook3.xlsx]sheet1'!$A$1

Given A,B and C, I can create D but I find no way to use it in a formula in E.

I am not able to reference the above string in D in a formula. Also INDIRECT does not work.

Answer 1

According to Excel 2007 Help on INDIRECT:

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

Is having the relevant files open an option?

If not, sounds like some scripting might be required...

Answer 2

I think you need only get rid of the single-quote characters to use INDIRECT. Works for me in Excel 2003 on Windows NT.

A13----B13----C13--D13----------------------E13-----------

w1.xls Sheet1 $A$1 ="["&A13&"]"&B13&"!"&C13 =INDIRECT(D13)

Answer 3

Thanks to everybody contribution and further experimentation, I can now assert: There are a combination of factors that have to be in place for the external reference to work.

1. If the reference refers to another workbook (an external reference), the other workbook must be open
2. Get rid of the single-quote characters to use INDIRECT
3. The workbook filename cannot contain white spaces

For example, given w1.xls and w 2.xls both open, I can create a new workbook with the following:

A---------B-------C-----D-------------------------E---------------------------------
w1.xlsx   sheet1  $A$1  ="["&A13&"]"&B13&"!"&C13  =INDIRECT(D13)  'will work
w 2.xlsx  sheet1  $A$1  ="["&A14&"]"&B14&"!"&C14  =INDIRECT(D14)  'Does NOT work

To this point, the great limitation of this approach is that all workbooks must be open and the workbook name cannot contain blanks.

Answer 4

I wanted to just add a comment but I ain't got the reputation yet. Another problem with INDIRECT -- the indirectly referenced cell will make it into Excel's dependency tree as it is calculated within the function. (Formula auditing should show just a link to D13 not to another workbook)

To get around this INDIRECT is a volatile function, which means it recalculates whenever there's any change within the workbooks. Heavy use of INDIRECT, like any volatile function, can slow down Excel recalculation if you have a weighty sheet.