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?

On Tue, 18 Sep 2007 17:54:01 -0700, Apexman
wrote:

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

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

On Sep 18, 5:54 pm, Apexman wrote:
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?

Randy and Dick:

Thanks for your replies. Randy, Yes. i can use Index(). Last night I had
just discovered that Index() also gives me the functionality that I need...
and that it is suitably behaved w.r.t. contained external references.

Thanks again.

"Randy Harmelink" wrote:

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

On Sep 18, 5:54 pm, Apexman wrote:
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?