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

[Guest]

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

--

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?