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?
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> >
> >
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks