Excel 2002 VLOOKUP in another workbook returns #VALUE

[EXCELDEV]

Excel 2002 VLOOKUP in another workbook returns #VALUE if the workbook is
closed. I have the full path and file name in the formula, but it only
populates correctly if I open the "looked up" workbook.
Is this a Excel 2002 bug, or is there a way around it?
My work around for now is to open the workbook, but set my macro to:
Application.ScreenUpdating = False

 

[Bob Bridges]

No solution, just a comment: I ran into the same thing at a recent
engagement; I'm not sure whether they were using 2003 or 2007 but it sure
wasn't 2002. Therefore I don't think it was a bug.

It didn't always work like that, only for external spreadsheets that resided
in certain places. I didn't spend a lot of time working on it, for in my
case I pretty much had to open the other spreadsheet anyway so it didn't
inconvenience me much. But I had a theory, at the time, that the references
that worked correctly were to documents that were on my own hard drive and
home directory, and in the cases where I had to open the external document to
get the references to work, they resided on a different drive shared by my
department and others. So I always figured it had to do with the document
location. But as I said I never got around to testing my theory.