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

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

Ultimately this will help me creating copies of this and many other
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 close
the file (and then refresh the formula calcs) the error returns.

Any idea what I'm doing wrong?


--
AndrewPace
------------------------------------------------------------------------
AndrewPace's Profile: http://www.excelforum.com/member.php...o&userid=33417
View this thread: http://www.excelforum.com/showthread...hreadid=532354

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


"AndrewPace" wrote
in message ...

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

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

Ultimately this will help me creating copies of this and many other
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 close
the file (and then refresh the formula calcs) the error returns.

Any idea what I'm doing wrong?


--
AndrewPace
------------------------------------------------------------------------
AndrewPace's Profile:
http://www.excelforum.com/member.php...o&userid=33417
View this thread: http://www.excelforum.com/showthread...hreadid=532354

Many thanks for your quick reply.


--
AndrewPace
------------------------------------------------------------------------
AndrewPace's Profile: http://www.excelforum.com/member.php...o&userid=33417
View this thread: http://www.excelforum.com/showthread...hreadid=532354