How do I read a file name into =[BookA.xls]Sheet1!$a$1?

[Facilitator]

I want to link Excel spreadsheets by formula, rather than by manual
manipulation. I want to put data from a series of standard-format weekly
spreadsheets into 1 summary spreadsheet.

Manually the process is:

1. Open Spreadsheet A with a lot of data and calculations in it in columns
2. Open Spreadsheet B into which I want to put selected data from A
3. From a cell in B, enter �=� and then switch to A, click on the top cell
from which I want data and press Enter
4. The formula in the cell then reads in the format �=[BookA.xls]Sheet1!$A$1�
5. Modify that formula by removing the $ signs i.e. convert $A$1 to A1
6. Manually copy that formula down the column

This achieves the objective but is labour and skill intensive.

What I would like to do is to be able to add the names of the weekly
spreadsheets to the top of a column (e.g. BookA, BookB and so on) and for the
formulae in the column to read that name into the cell formulae. I have tried
to do this but just get error messages.

Is there a way to do what I want to do or must it be done manually every time?

[Max]

One way is to use INDIRECT*

> ... "=[BookA.xls]Sheet1!$A$1"

With B1 across containing: BookA, BookB, ...
Put in B2: =INDIRECT("["&B$1&".xls]Sheet1!A"&ROW(A1))
Copy B2 across & fill down

*The source books: BookA, BookB, ... need to be open simultaneously
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Facilitator" <[email protected]> wrote in message
news:[email protected]...
> I want to link Excel spreadsheets by formula, rather than by manual
> manipulation. I want to put data from a series of standard-format weekly
> spreadsheets into 1 summary spreadsheet.
>
> Manually the process is:
>
> 1. Open Spreadsheet A with a lot of data and calculations in it in columns
> 2. Open Spreadsheet B into which I want to put selected data from A
> 3. From a cell in B, enter "=" and then switch to A, click on the top cell
> from which I want data and press Enter
> 4. The formula in the cell then reads in the format
"=[BookA.xls]Sheet1!$A$1"
> 5. Modify that formula by removing the $ signs i.e. convert $A$1 to A1
> 6. Manually copy that formula down the column
>
> This achieves the objective but is labour and skill intensive.
>
> What I would like to do is to be able to add the names of the weekly
> spreadsheets to the top of a column (e.g. BookA, BookB and so on) and for
the
> formulae in the column to read that name into the cell formulae. I have
tried
> to do this but just get error messages.
>
> Is there a way to do what I want to do or must it be done manually every
time?
>
>