OFFSET function returning #VALUE when using external references

  • Thread starter Thread starter AndrewPace
  • Start date Start date
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mapping integers to strings 5
Offset/match returns #value error 8
OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table 6
External reference for Offset formula 2
Indirect reference causes statistical function to throw up a #NUM! 10
Using the return from ADDRESS within another formula? 5
Limitations when Combining OFFSET and MATCH to return a cell refer 1
External References in Functions 2

Back
Top