Reference to multiple workbooks?

[MechEng2005]

Is it possible to get data from multiple files into a single "Summary" book? I was trying to create a form for estimating purposes. Some of our projects are quite large and broken into parts A, B, C, etc...

So, I wanted to make a folder for estimating that would contain one file "Project.xls", another file "A.xls", "B.xls", etc...

I managed to do this and when the folder is copied the links to other workbooks in the Project file DOES always look in the same directory, so that's good. However, it doesn't seem to update the data unless I open all the other files. Excel only seems to be able to retrieve data from a single other file. So typically when I open "Project.xls" it will update the data for "A.xls" but will not update the others.

I am currently using Excel 97 (maybe that's part of the issue...)

I appologize that I can't be more clear about when it updates and things, but I eventually gave up and just created one file with many tabs.

Any ideas? Thanks, MechEng2005

 

[joerd]

You can set up a database query instead.
Go to Data/Import External Data/New Database Query... and link to the spreadsheets. It helps if the source spreadsheets have range names defined for the exported data.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[MechEng2005]

Thank you!