| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
get data from closed workbooks!
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 --- 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 |
|
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
get data from closed workbooks!
Try this to get your data
http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Martyn" wrote in message ... 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 --- 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 |
|
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
get data from closed workbooks!
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" wrote in message ... Try this to get your data http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Martyn" wrote in message ... 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 --- 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 --- 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 |
|
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
get data from closed workbooks!
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 "Martyn" wrote in message ... 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" wrote in message ... Try this to get your data http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Martyn" wrote in message ... 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 --- 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 --- 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 |
|
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
get data from closed workbooks!
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 "Ron de Bruin" wrote in message ... 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 --- 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.714 / Virus Database: 470 - Release Date: 02.07.2004 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting Data from Closed Workbooks | Excel Worksheet Functions | |||
| Referencing Data in Closed Workbooks | Excel Discussion (Misc queries) | |||
| Keeping data once referenced workbooks are closed. | Excel Discussion (Misc queries) | |||
| SAVING DATA TO CLOSED WORKBOOKS | Excel Worksheet Functions | |||
| Copying Data from closed workbooks | Excel Programming | |||
Linear Mode
