reffering in a formula to a file

[Lee]

Hello,

I want a function to work with data in different
workbooks. Let's say I want to sum all data in a column in
a worksheet whose name I have in column A - e.g. in A1 I
have file1.xls, in A2 I have file2.xls etc. I want to
write the function into B1:
=SUM('c:\myfolder\[file1.xls]Sheet1'!$A1:$A10000)
But instead of writing manually file1.xls I want to just
reference the A1 cell with the workbook name. Because I
want to fill the formula down the column B and in each
cell of B column I want to have a corresponding filename
as it is written in A column.

Has anybody idea how to do it?
Lee

 

[Bob Phillips]

=SUM(INDIRECT("'"&A1&"Sheet1'!$A1:$A10000))

A1 would contain 'c:\myfolder\[file1.xls]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Use INDIRECT:

=SUM(INDIRECT("'["&A1&"]Sheet1'!A1:A10000"))

Note: the source file must be open.

HTH
Jason
Atlanta, GA

 

[Lee]

I wrote it =SUM(INDIRECT("'"&A1&"Sheet1'!$A1:$A10000"))
A1 contains 'O:\myfolder\[file1.xls]

but I get #REF error in the cell.
I have excel XP
Where is the problem?
Thanks
Lee

-----Original Message-----
=SUM(INDIRECT("'"&A1&"Sheet1'!$A1:$A10000))

A1 would contain 'c:\myfolder\[file1.xls]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello,

I want a function to work with data in different
workbooks. Let's say I want to sum all data in a column in
a worksheet whose name I have in column A - e.g. in A1 I
have file1.xls, in A2 I have file2.xls etc. I want to
write the function into B1:
=SUM('c:\myfolder\[file1.xls]Sheet1'!$A1:$A10000)
But instead of writing manually file1.xls I want to just
reference the A1 cell with the workbook name. Because I
want to fill the formula down the column B and in each
cell of B column I want to have a corresponding filename
as it is written in A column.

Has anybody idea how to do it?
Lee

.

 

[Guest]

Thanks, the problem is that I cannot have the file open. I
have around 80 files...
Is there a workaround?

Lee

-----Original Message-----
Use INDIRECT:

=SUM(INDIRECT("'["&A1&"]Sheet1'!A1:A10000"))

Note: the source file must be open.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello,

I want a function to work with data in different
workbooks. Let's say I want to sum all data in a column in
a worksheet whose name I have in column A - e.g. in A1 I
have file1.xls, in A2 I have file2.xls etc. I want to
write the function into B1:
=SUM('c:\myfolder\[file1.xls]Sheet1'!$A1:$A10000)
But instead of writing manually file1.xls I want to just
reference the A1 cell with the workbook name. Because I
want to fill the formula down the column B and in each
cell of B column I want to have a corresponding filename
as it is written in A column.

Has anybody idea how to do it?
Lee


.

.

 

[Don Guillett]

As mentioned, you can use indirect if the source file is open or you may
want to use edit/replace to change your formulas.

 

[Frank Kabel]

Hi
INDIRECT can't work with closed files. So this can't work. for
alternatives see:
http://tinyurl.com/2c62u

--
Regards
Frank Kabel
Frankfurt, Germany

I wrote it =SUM(INDIRECT("'"&A1&"Sheet1'!$A1:$A10000"))
A1 contains 'O:\myfolder\[file1.xls]

but I get #REF error in the cell.
I have excel XP
Where is the problem?
Thanks
Lee

-----Original Message-----
=SUM(INDIRECT("'"&A1&"Sheet1'!$A1:$A10000))

A1 would contain 'c:\myfolder\[file1.xls]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello,

I want a function to work with data in different
workbooks. Let's say I want to sum all data in a column in
a worksheet whose name I have in column A - e.g. in A1 I
have file1.xls, in A2 I have file2.xls etc. I want to
write the function into B1:
=SUM('c:\myfolder\[file1.xls]Sheet1'!$A1:$A10000)
But instead of writing manually file1.xls I want to just
reference the A1 cell with the workbook name. Because I
want to fill the formula down the column B and in each
cell of B column I want to have a corresponding filename
as it is written in A column.

Has anybody idea how to do it?
Lee

.