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

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 3

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)