+ Reply to Thread
Results 1 to 3 of 3

concatenating cell references

  1. #1
    nick.pattison
    Guest

    concatenating cell references

    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?




  2. #2
    Ken Wright
    Guest

    Re: concatenating cell references

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




  3. #3
    nick.pattison
    Guest

    Re: concatenating cell references

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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1