OFFSET question

[J Donahue]

I seem to be having a problem using the OFFSET function.
The problem seems to be that it does not work when linking
to another file. Example, in file 1 I have a grid that is
5X5 filled with numbers in cells A1..E5 as follows:

A B C D E
1 2 3 4 5 6
2 3 4 5 6 7
3 4 5 6 7 8
4 5 6 7 8 9
5 6 7 8 9 10

From file 2 I have set the formula as =OFFSET(A1,3,3)
which should return the result of 8 (3 cols over and 3
rows down from cell A1). The formula seems to work fine
as long as my source document is open but when the sourece
document is closed, I get a #VALUE! error in the cell with
my OFFSET formula.

Can anyone help me out here?

Thanks

J Donahue

 

[Ed]

Worked fine when I tried it, as long as you include references to your data:
=OFFSET([File2]Sheet1!$A$1,3,3)
Build your data fields (File 1) first. Then build your linked file (File 2)
by going to the cell you want data to appear in, enter =OFFSET( , then going
to File 1 and clicking in the cell you will offset from. Finish by putting
in the offset. Excel will fill in the address of the data.

BTW, I assume I you're going to the trouble of using OFFSET, there is a
reason why you can't just link to the right cell in the first place? As in:
using =OFFSET([Book2]Sheet1!$A$1,3,3) instead of =[Book2]Sheet1!$D$4?

HTH
Ed

 

[Ken Wright]

You didn't update your links after closing the source file then, as if you had
you would have recieved a VALUE error. Offset works on a range, and Excel does
not like to do that on a closed workbook. It will cope quite comfortably with
an array, but a range is a no no. You can get round this depending on what you
are doing, and some examples of what would work are things such as VLOOKUP,
SUMPRODUCT, etc

You need to use the full filepath if you are to return any data from a closed
workbook, regardless of the function you use, and there is really no substitute
for trial and error. Just as an aside, INDIRECT will not work on a closed
workbook, so you cannot use this to build your formula.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

Worked fine when I tried it, as long as you include references to your data:
=OFFSET([File2]Sheet1!$A$1,3,3)
Build your data fields (File 1) first. Then build your linked file (File 2)
by going to the cell you want data to appear in, enter =OFFSET( , then going
to File 1 and clicking in the cell you will offset from. Finish by putting
in the offset. Excel will fill in the address of the data.

BTW, I assume I you're going to the trouble of using OFFSET, there is a
reason why you can't just link to the right cell in the first place? As in:
using =OFFSET([Book2]Sheet1!$A$1,3,3) instead of =[Book2]Sheet1!$D$4?

HTH
Ed

I seem to be having a problem using the OFFSET function.
The problem seems to be that it does not work when linking
to another file. Example, in file 1 I have a grid that is
5X5 filled with numbers in cells A1..E5 as follows:

A B C D E
1 2 3 4 5 6
2 3 4 5 6 7
3 4 5 6 7 8
4 5 6 7 8 9
5 6 7 8 9 10

From file 2 I have set the formula as =OFFSET(A1,3,3)
which should return the result of 8 (3 cols over and 3
rows down from cell A1). The formula seems to work fine
as long as my source document is open but when the sourece
document is closed, I get a #VALUE! error in the cell with
my OFFSET formula.

Can anyone help me out here?

Thanks

J Donahue

 

[Andy Wiggins]

But, SQL.REQUEST will work on closed workbooks.

Here is an example that might be a help:
http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"