I am using a cell reference of the form:

'[January 05 Room Diary.xls]WE 07-01-05'!K17:N20

how can I concatenate such a reference from its filename, sheetnam etc and
still use it in a function?

If you are building a reference from parts of a string, then you need to use
INDIRECT to convert it into a valid reference, BUT, INDIRECT will not work
on closed workbooks.

To quote a previous post from Harlan Grove:-

Quote
If you want to avoid using macros and use formulas instead, your *only*
options
are to download and install Laurent Longre's MOREFUNC.XLL add-in, available
at

http://longre.free.fr/downloads/Morefunc.exe


(which is an installer for the add-in). It's INDIRECT.EXT works with most
versions of Excel (e.g., my laptop running Excel 97 SR-2 under NT 4 SP-6)
but
not all (e.g., my wife's PC running Excel 2000 SR-3 under Windows Me).

/Quote

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"nick.pattison" wrote in message
...
I am using a cell reference of the form:

'[January 05 Room Diary.xls]WE 07-01-05'!K17:N20

how can I concatenate such a reference from its filename, sheetnam etc and
still use it in a function?

thanks

"Ken Wright" wrote:

If you are building a reference from parts of a string, then you need to use
INDIRECT to convert it into a valid reference, BUT, INDIRECT will not work
on closed workbooks.

To quote a previous post from Harlan Grove:-

Quote
If you want to avoid using macros and use formulas instead, your *only*
options
are to download and install Laurent Longre's MOREFUNC.XLL add-in, available
at

http://longre.free.fr/downloads/Morefunc.exe


(which is an installer for the add-in). It's INDIRECT.EXT works with most
versions of Excel (e.g., my laptop running Excel 97 SR-2 under NT 4 SP-6)
but
not all (e.g., my wife's PC running Excel 2000 SR-3 under Windows Me).

/Quote

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"nick.pattison" wrote in message
...
I am using a cell reference of the form:

'[January 05 Room Diary.xls]WE 07-01-05'!K17:N20

how can I concatenate such a reference from its filename, sheetnam etc and
still use it in a function?