reffering in a formula to a file


L

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
 
Ad

Advertisements

B

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)
 
J

Jason Morin

Use INDIRECT:

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

Note: the source file must be open.

HTH
Jason
Atlanta, GA
 
L

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


.
 
G

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


.
.
 
D

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.
 
Ad

Advertisements

F

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)

Lee said:
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


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top