External Reference Data Updates.

[Apexman]

I am using Excel 2003. I have a cell referencing an external workbook (that
is on my own computer). If the external reference is within the OFFSET or
INDIRECT function, it gets a #ref error if the supporting workbook is not
OPEN. When I open the supporting workbook, the same formula works fine. I
tried external references that are not imbedded in functions, and they work
fine (supporting workbook closed or open). I also tried the SUM function,
and it too works with the supporting workbook open or closed. Am I doing
something wrong? Is there some limitation that I cannot find documented? Is
there a know bug? Can anyone help?

 

[Dick Kusleika]

I am using Excel 2003. I have a cell referencing an external workbook (that
is on my own computer). If the external reference is within the OFFSET or
INDIRECT function, it gets a #ref error if the supporting workbook is not
OPEN. When I open the supporting workbook, the same formula works fine. I
tried external references that are not imbedded in functions, and they work
fine (supporting workbook closed or open). I also tried the SUM function,
and it too works with the supporting workbook open or closed. Am I doing
something wrong? Is there some limitation that I cannot find documented? Is
there a know bug? Can anyone help?

OFFSET and INDIRECT are volatile functions, that is, they calculate every
time the worksheet calculates regardless if any of their dependents have
changed. Generally (or maybe always), volatile functions don't work with
external references to closed workbooks. Even if there are some volatile
functions that do, I know that these two don't.

 

[Randy Harmelink]

Can you use INDEX() instead of the OFFSET() function?