Strange behaviour of OFFSET

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)

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

Richard Schollar said:

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)

Click to expand...

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?

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.

Glory said:

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.

Click to expand...

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

Richard Schollar said:

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)

Click to expand...

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