get data from closed workbooks!

[Martyn]

Hi,
I have five workbooks named "data1.xls", "data2.xls",...,"data5.xls".
On each and every Sheet1 from ranges T1:T30 holds data that I want to use on
a seperate workbook.
On a seperate workbook (say named "final.xls") I want to get this data and
calculte the averages like that:

I want to get the values from all closed workbooks "data1.xls", "data2.xls",
"data3.xls", "data4.xls", "data5.xls" Sheet1 T1 values, sum them and devide
by 5 and display that resulting number on "final.xls" Sheet1 T1 cell. Same
operation with data1.xls,data2.xls,data3.xls,data4.xls,data5.xls Sheet1 T2
values, sum them and devide by 5 and display that figure on "final.xls"
Sheet1 T2...etc
How am I solve this?

Thanks in advance
Martyn

 

[Ron de Bruin]

Try this to get your data
http://www.rondebruin.nl/copy3.htm

 

[Martyn]

Hi Ron,
Thanks for your link. May I ask a follow-up question?
=[data1.xls]Sheet1!$T$1
gets data from an open workbook "data1.xls" to the target workbook
"final.xls".
Can this addressing technique be modified so that it can retrieve the data
from the closed workbook?
TIA

 

[Ron de Bruin]

Hi Martyn

='C:\Data\[test1.xls]Sheet1'!$A1

If you use the full path it will do what you want.
Only if you open the workbook you get a dialog that ask
you if you want to update the links



--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi Ron,
Thanks for your link. May I ask a follow-up question?
=[data1.xls]Sheet1!$T$1
gets data from an open workbook "data1.xls" to the target workbook
"final.xls".
Can this addressing technique be modified so that it can retrieve the data
from the closed workbook?
TIA

Try this to get your data
http://www.rondebruin.nl/copy3.htm

---
Outgoing mail is certified Virus Free.
(Giden posta virüssüz olarak belgelendi.)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.713 / Virus Database: 469 - Release Date: 30.06.2004

 

[Martyn]

Hi Ron,
This is it!..
I was looking all over the place on my PC to locate an example including
such a syntax.
Thanks a lot. You are among my favorite excel masters on this NG.
Cheers
Marytn

Hi Martyn

='C:\Data\[test1.xls]Sheet1'!$A1

If you use the full path it will do what you want.
Only if you open the workbook you get a dialog that ask
you if you want to update the links