OFFSET function returning #VALUE when using external references

[AndrewPace]

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?

[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


"AndrewPace" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[AndrewPace]

Many thanks for your quick reply.