Linking cells from different workbooks?

[SPIVA2146]

I am trying to link the same cells with different workbooks. I want to be able to only have to change the link for the workbook, and leave the cell references the same.
I could put in: ='c:\database[file1.xls]Sheet1'!C15
for each cell, that works.
but I want to reference different files; file1, file2, file3 etc.
This is what i've got so far
A1='c:\database[file1.xls]sheet1'
A2=!C15
A3=!C16
A4=!C17
B1='c:\database[file2.xls]sheet1'
B2=!C15
B3=!C16
B4=!C17

I hope this makes sence...
Any help would be great!!!

Thanks
spiva

 

[Banker1]

Try the INDIRECT function:

Something like this:

A1="c:\database"&indirect($A$1)&"sheet1"
A2=!C15
A3=!C16
A4=!C17
B1='c:\database"&indirect($B$1)&"sheet1"
B2=!C15
B3=!C16
B4=!C17

Where A1 and B1 contain the text file1.xls and file2.xls, respectively. Update accordingly.

 

[SPIVA2146]

I couldn't get the INDIRECT function to work.
I'm sorry, I left out one thing in my example.

A1='c:\database[file1.xls]sheet1'
A2=(A1)!C15
A3=(A1)!C16
A4=(A1)!C17
B1='c:\database[file2.xls]sheet1'
B2=(B1)!C15
B3=(B1)!C16
B4=(B1)!C17

 

[Banker1]

Looking back at what I originally suggested... file1.xls and file2.xls should look like [file1.xls] and [file2.xls] (forgot the brackets). Give that a shot.

 

[Banker1]

Upon further reading I found this:

http://www.contextures.com/xlFunctions05.html

Create a Reference To a Different Workbook is towards the bottom of the pages and says, "An INDIRECT formula can refer to cells in other workbooks, but will return a #REF! error if that workbook is closed." so my suggestion probably isn't the best method to achieve what you're trying to do.

 

[arkusM]

Indirect() does not work with closed external files. I have tried!!

Laurent Longre has a UDF that may work, his site is down/gone but I found a link to download his free addin.

Try:http://www.dirfile.com/publisher/laurent-longre.html