Offset - external sheet referencing

[joachimb]

[Excel version 97 - SR2]

When I try to offset a cell in an external sheet (i.e. one outside th
workbook I currently work in), Excel returns #VALUE instead of th
actual value in the cell offset.

On the other hand, if I keep the referenced sheet and the workbook ope
at the same time, Excel returns the value.

Why cannot I not offset a closed sheet/workbook

 

[Andy B]

Hi

AFAIK when a workbook is closed, the refernce to it has to include the
pathname and filename, as well as the sheet reference and cell reference.
The length of all of this information could well be causing the formula to
be too long.

 

[joachimb]

Thanks for the tip Andy.

However, I have several other cell references that are far longer tha
the offset reference, so lenght alone does not explain the error.

Other ideas

 

[Andy B]

Hi

I had exactly the same problem a few weeks ago and it was the length of the
pathname+filename+sheetname+cell reference that caused my problem! I don't
know what else it could be.
Sorry.

 

[Peo Sjoblom]

There are many formulas that don't work on closed workbooks, offset is one
of them,
others are indirect, countif and sumif. There are some workarounds albeit
not very simple

http://groups.google.com/[email protected]