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 said:
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?
