Strange behaviour of OFFSET

[Dag]

I have two workbooks: book1.xls and book2.xls
In book2.xls I have in cell A1:
='C:\[Book1.xls]Sheet1'!$D$1
and in cell B1:
=OFFSET('C:\\[Book1.xls]Sheet1'!$A$1,0,3)

When Book1.xls is opened, everything works fine. When Book1.xls is closed, only formula in A1 returns result; formula in
cell B1 returns #VALUE!

I've noticed this problem only with OFFSET function. Any MSOft developer here?

 

[Richard Schollar]

Offset won't work with an external link if the linked to workbook is closed. SUMIF/COUNTIF are similarly restricted.

You could use an INDEX formula instead if you wished along the lines of:

=INDEX('C:\\[Book1.xls]Sheet1'!$A$1:$Z$1,4)

 

[Glory]

Richard, is it possible to get a range from a closed workbook without using paste links or other methods (eg ADO)?

 

[Dag]

Offset won't work with an external link if the linked to workbook is closed. SUMIF/COUNTIF are similarly restricted.
You could use an INDEX formula instead if you wished along the lines of:
=INDEX('C:\\[Book1.xls]Sheet1'!$A$1:$Z$1,4)

Thank you Richard. But I can't get needed data without using OFFSET.
http://www.mrexcel.com/forum/showthread.php?t=538554
Should ADO be used in cell formula (function)? Without VBA?

 

[Glory]

http://www.excelforum.com/excel-pro...ynamic-range-in-closed-workbook-with-ado.html
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
http://www.w3schools.com/ado/ado_intro.asp

Dag: The first link will take you to a page that explains how to set a certain property in the referenced workbook to update each time the workbook is edited. That property specifies a dynamic range which can then be obtained using ADO.

The second link explains how to use ADO. You can type ADO code directly into a standard module using Excel's VBA editor.

I think there's another way to solve your problem. See the following link:

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file

You may already know about this. I was able to use this method to get an offset value when there was a clear, repeating pattern in the data, by setting a variable for "a" that would update based on the location of the data.

You can keep a single cell in the reference sheet as a delimited list. Set a worksheet change event in the referenced workbook to update the value of that cell with its own contents plus the contents of any changed cells in the column where you've stored the data you want to get, combined with the data that's offset which you would normally search among using "find".

Use the function at the last link above to grab that reference cell's contents. Use "instr" to find the information you need and "split" to weed out the range associated with the information.

Then you have your data.

 

[Dag]

http://www.excelforum.com/excel-pro...ynamic-range-in-closed-workbook-with-ado.html
<o></o>http://www.w3schools.com/ado/ado_intro.asp
* * *
Then you have your data.

Thank you Glory.
I'll try to not work with macros... Big mess in large project from my experience.

 

[UDcc123]

Offset won't work with an external link if the linked to workbook is closed. SUMIF/COUNTIF are similarly restricted.

You could use an INDEX formula instead if you wished along the lines of:

=INDEX('C:\\[Book1.xls]Sheet1'!$A$1:$Z$1,4)

Just wanted to say thank you. I had the same offset-external link issue and your index solution worked perfectly.