OFFSET function returning #VALUE when using external references

A

AndrewPace

I am trying to setup an external reference using the offset function, a
in:

=OFFSET('\\file_path\[filename.xls]worksheet'!$CR$20,0,0)

Ultimately this will help me creating copies of this and many othe
external references where data will accumulate in predictable ways
The problem is, the formula as shown returns a #VALUE error.

The function will work if the file is currently open, but after I clos
the file (and then refresh the formula calcs) the error returns.

Any idea what I'm doing wrong
 
P

Peo Sjoblom

Nothing wrong, this is a fact, Offset does not work with workbooks that are
closed.

INDEX can be used many times to do similar things that OFFSET can do and it
will work with closed workbooks. You need to include the whole possible
array in the first part of index but in many cases it can be used as a
replacement


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
Top