Linking to external workbooks

hi Matt,

This can be done using the Indirect function but as the Excel 2003 Help files state...

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.

(Others may be able to suggest alternative methods)

Is there any way that you can redesign your file structure to overcome this?

- possibly by "reversing the links"?
If the links were reversed you could use a vlookup from the "Jan1", "Jan2" files etc to the single "Base" file.

- or using a single file with a "database structure" containing the info for every day?

Another (more complicated?) option is that someone sets up a macro for you which goes through the existing files & updates the Base file with the values from the daily files...

Or a slow manual way could be to just use copy & paste (+ typing) to type in the non-existent file references eg
cell B4 is ='C:\Documents and Settings\HP_Owner\Desktop\[jan1.xls]Sheet1'!$B$2
select this cell, press [F2], press [ctrl + shift + home], [ctrl + c], [Enter] (have Tools-Options-Edit setup to move the selection to the right after enter).
This selects cell C4, press [ctrl + v], [F2], and change the file name ie:
cell c4 would be ='C:\Documents and Settings\HP_Owner\Desktop\[jan1.xls]Sheet1'!$B$2
change this to ='C:\Documents and Settings\HP_Owner\Desktop\[jan2.xls]Sheet1'!$B$2
press [enter] & then [esc] when Excel asks where you want to update the values from.


hth
Rob