File names within formulae

[POKEY]

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

 

[David Biddulph]

Go to Excel help & look up the INDIRECT function.

 

[Dave Peterson]

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.

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

 

[Shane Devenshire]

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