Help - is it possible to reference data from another spreadsheet by using a
named range or otherwise within the formula? e.g. You want to retrieve data
from cell A1 in another spreadsheet named "accounts january.xls" - you would
normally type =c:\[accounts january.xls]'!A1. Each month the name of the
spreadsheet source changes to "account february.xls" etc..etc.. This formula
exists in many cells - is it possible to name this "changing" spreadsheet
name by referencing all these formulas to one cell which contains the name of
the spreadsheet source? e.g =c:\[=b2].xls'!A1

Go to Excel help & look up the INDIRECT function.
--
David Biddulph

"POKEY" wrote in message
...
Help - is it possible to reference data from another spreadsheet by using
a
named range or otherwise within the formula? e.g. You want to retrieve
data
from cell A1 in another spreadsheet named "accounts january.xls" - you
would
normally type =c:\[accounts january.xls]'!A1. Each month the name of the
spreadsheet source changes to "account february.xls" etc..etc.. This
formula
exists in many cells - is it possible to name this "changing" spreadsheet
name by referencing all these formulas to one cell which contains the name
of
the spreadsheet source? e.g =c:\[=b2].xls'!A1

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.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

POKEY wrote:

Help - is it possible to reference data from another spreadsheet by using a
named range or otherwise within the formula? e.g. You want to retrieve data
from cell A1 in another spreadsheet named "accounts january.xls" - you would
normally type =c:\[accounts january.xls]'!A1. Each month the name of the
spreadsheet source changes to "account february.xls" etc..etc.. This formula
exists in many cells - is it possible to name this "changing" spreadsheet
name by referencing all these formulas to one cell which contains the name of
the spreadsheet source? e.g =c:\[=b2].xls'!A1

--

Dave Peterson

Hi,

You can also use something like this

=CHOOSE(A1,c:\[accounts january.xls]'!A1,c:\[accounts february.xls]'!A1,...)

And in A1 enter the month number 1-12. You need to complete the above
formua for all 12 months.

The advantage in using this is that the external files don't need to be open.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"POKEY" wrote:

Help - is it possible to reference data from another spreadsheet by using a
named range or otherwise within the formula? e.g. You want to retrieve data
from cell A1 in another spreadsheet named "accounts january.xls" - you would
normally type =c:\[accounts january.xls]'!A1. Each month the name of the
spreadsheet source changes to "account february.xls" etc..etc.. This formula
exists in many cells - is it possible to name this "changing" spreadsheet
name by referencing all these formulas to one cell which contains the name of
the spreadsheet source? e.g =c:\[=b2].xls'!A1