Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Thanked 0 Times in 0 Posts

    Dynamic references to cells in external files (Excel 2000 / XP)

    I'm trying to get excel to be able to dynamically reference external files (other excel spreadsheets).

    I want to be able to type in a file name, and have functions look at different cells within that file. (In fact, I want a master sheet to be able to look up several other spreadhseets, according to which spreadsheet names I type in).

    I can get it to work using =INDIRECT(), but it only works when the other files are open. The second the external files close, the results all turn to #REFs. And when I open the main file, they all evaluate to #REFs if the external files aren't open.

    Is there another way I can get this to work, that behaves as if I had typed the address into the formula directly (ie it will work if the file isn't open, and only update when the spreadsheet is openned).


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Dynamic references to cells in external files (Excel 2000 / XP)

    What you note is (IMHO) a MAJOR flaw in indirect: references MUST be open.

    Workaround usually involves, making DIRECT references (with the file names hardcoded in, ie LINKS to the other external files) in 1 worksheet in the workbook (or in several worksheets in another workbook, that will be opened).

    Once you have a table of these DIRECT references / links, You use INDIRECT to LOOKUP/MATCH items in these sheets to the particular items of interest.

    Downside is that you must make DIRECT references to all the cells of interest in the external files (not a MAJOR problem, if there is NOT too many),

    Upside: it allows some type of "indirect" references to these external files without having to have them all open.


  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Thanked 0 Times in 0 Posts

    Re: Dynamic references to cells in external files (Excel 2000 / XP)

    Perhaps of use. Do not know whether this works for Excel XP too, as it is leftover from pre 97 versions.

Posting Permissions

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