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

Topic

New Reply

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).

Help!!!

Reply | Quote

Replies

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.

Steve

Reply | Quote

Perhaps of use. Do not know whether this works for Excel XP too, as it is leftover from pre 97 versions.
http://j-walk.com/ss/excel/tips/tip82.htm%5B/url%5D

Reply | Quote