Extracting Data from other Workbooks

[mlv]

I have two Excel 2003 yearly Workbooks in the same directory and I require
one of them to extract data from the other.

Basically, the second workbook (which is a summary of the first workbook)
has to simply extract and display the values from 48 cells in the first
workbook.

The challenge (for me) is to arrange for this to happen automatically every
year, without me having to annually edit the 48 formulas in the second
workbook because the filename of the first workbook will change every year.

The yearly filename change is simple and predictable:

First workbook filename for this year : Expenses_2007-08

Next year the filename will be : Expenses_2008-09, and so on...

The second workbook could have a cell (assume A1) that carries the
appropriate filename year date (i.e. 2007-08, 2008-09, 2009-10, etc).

The first workbook filename prefix ( Expenses_ ) will remain constant across
the years.

Is it possible to create a formula in the appropriate cells in the second
workbook (Summary) that will automatically construct the path to the
appropriate cell in the first workbook, and then extract the cell value?
Something like :

='["Expenses_" & (Cell A1) & ".xls"]Month1'!B2

Alternatively, the second workbook (Summary), Cell A1 could carry the full
filename of the current first workbook (Expenses) that it has to access,
which could be entered manually every year (i.e. Cell A1 :
Expenses_2007-08.xls). Then the formula would be something like:

='[ (Cell A1) ]Month1'!B2

Can anyone help with the formula, or perhaps suggest a better approach?

TIA

 

[Dave Peterson]

If the formula only changes once a year, I think I'd just select the cells with
the formula and do an edit|replace (or edit|links|Change links).

But...

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

I have two Excel 2003 yearly Workbooks in the same directory and I require
one of them to extract data from the other.

Basically, the second workbook (which is a summary of the first workbook)
has to simply extract and display the values from 48 cells in the first
workbook.

The challenge (for me) is to arrange for this to happen automatically every
year, without me having to annually edit the 48 formulas in the second
workbook because the filename of the first workbook will change every year.

The yearly filename change is simple and predictable:

First workbook filename for this year : Expenses_2007-08

Next year the filename will be : Expenses_2008-09, and so on...

The second workbook could have a cell (assume A1) that carries the
appropriate filename year date (i.e. 2007-08, 2008-09, 2009-10, etc).

The first workbook filename prefix ( Expenses_ ) will remain constant across
the years.

Is it possible to create a formula in the appropriate cells in the second
workbook (Summary) that will automatically construct the path to the
appropriate cell in the first workbook, and then extract the cell value?
Something like :

='["Expenses_" & (Cell A1) & ".xls"]Month1'!B2

Alternatively, the second workbook (Summary), Cell A1 could carry the full
filename of the current first workbook (Expenses) that it has to access,
which could be entered manually every year (i.e. Cell A1 :
Expenses_2007-08.xls). Then the formula would be something like:

='[ (Cell A1) ]Month1'!B2

Can anyone help with the formula, or perhaps suggest a better approach?

TIA