Offset, References, Help!

[cdegar01]

Hello all. I have a bit of a problem.

I have two workbooks, [Book1] and [Book2]. In [Book2], I have a cell A
with "=[Book1]Sheet1!$B$2" in it. The result is that A1 read
"Operating Margins."

What I want to know how to do is create formulas in [Book2] that wil
return values in [Book1] offset from the reference in A1.

In the end, I will simply link on cell in [Book2] to a cell in [Book1]
and maybe 20 cells in [Book2] will automatically have values that com
from [Book1].

I hope this isn't too confusing. Thanks

 

[Frank Kabel]

Hi
Not quite sure what you want to achieve, but let's give it a try. One
important note at the beginning. The following will work only within
the same workbook or if both workbooks are open (Reason: INDIRECT only
works for opened workbooks).
1. In Book2, cell A1 enter your reference cell for Book 1 as text (e.g.
'A20)
2. Now in book 2 you can create the following formula using OFFSET and
INDIRECT
=OFFSET(INDIRECT("'[Book1]Sheet1'!" & A1);row_offest;column_offset)

If you want to access a workbook which is not opened you have to use
VBA or use the following free add-in MOREFUNC
http://longre.free.fr/english

it includes a UDF "INDIRECT.EXT" which overcomes Excel's restrictions

HTH
Frank

 

[Harlan Grove]

=OFFSET(INDIRECT("'[Book1]Sheet1'!" & A1);row_offest;column_offset)

If you want to access a workbook which is not opened you have to use
VBA or use the following free add-in MOREFUNC
http://longre.free.fr/english

it includes a UDF "INDIRECT.EXT" which overcomes Excel's restrictions

INDIRECT.EXT won't help. The reason INDIRECT doesn't work when given text
references in closed workbooks is that INDIRECT can *only* return references
to range objects, and range objects *only* exist in *open* workbooks. Excel
treats hardcoded references to ranges in closed workbooks as arrays of
values, not as range objects. INDIRECT.EXT can return arrays as well as
range references, which is one of the reasons it works with closed
workbooks, but arrays of values are all that it returns from closed
workbooks. Since OFFSET only accepts references range objects as first
arguments, OFFSET as the outermost function will only work when its first
argument is a reference to a range in an *OPEN* workbook.

However, there is a convoluted way to do this. If the OP wants the value of
a single cell in the closed workbook, then the following approach should
work.

=INDIRECT.EXT("'"&DirectoryPath&"["&WorkbookName&"]"&WorksheetName&"'!"&
CELL("Address",OFFSET(A1,row_offset,column_offset)))

If the OP wants to dereference the values from a single area, multiple cell
range in a closed workbook, two CELL("Address",OFFSET(...)) calls would be
needed.

 

[Frank Kabel]

INDIRECT.EXT won't help. The reason INDIRECT doesn't work when given
text references in closed workbooks is that INDIRECT can *only*
return references to range objects, and range objects *only* exist in
*open* workbooks. Excel treats hardcoded references to ranges in
closed workbooks as arrays of values, not as range objects.
INDIRECT.EXT can return arrays as well as range references, which is
one of the reasons it works with closed workbooks, but arrays of
values are all that it returns from closed workbooks. Since OFFSET
only accepts references range objects as first arguments, OFFSET as
the outermost function will only work when its first argument is a
reference to a range in an *OPEN* workbook.

However, there is a convoluted way to do this. If the OP wants the
value of a single cell in the closed workbook, then the following
approach should work.

=INDIRECT.EXT("'"&DirectoryPath&"["&WorkbookName&"]"&WorksheetName&"'!"
&
CELL("Address",OFFSET(A1,row_offset,column_offset)))

If the OP wants to dereference the values from a single area,
multiple cell range in a closed workbook, two
CELL("Address",OFFSET(...)) calls would be needed.

Hi Harlan

you're right. Should have tested the combination of OFFSET and
INDIRECT.EXT (only uses INDIRECT.EXT without any other formulas).
Especially thanks for your explanation why INDIRECT.EXT did not work.
Though I remeber that some time ago you posted a UDF which was able to
access closed workbooks and also works within other fucntions like
OFFSET or VLOOKUP. Do you still have this UDF /wasn't able to find it
via google)

Regards
Frank

 

[Harlan Grove]

Though I remeber that some time ago you posted a UDF which was able to
access closed workbooks and also works within other fucntions like
OFFSET or VLOOKUP. Do you still have this UDF /wasn't able to find it
via google)

The UDF is named pull(), and it's in

http://www.google.com/[email protected]

However, it can't be used as the first argument to OFFSET for the same
reason INDIRECT.EXT can't - it doesn't return a reference to a range object.
OFFSET's first argument can only ever be a reference to a range in an open
workbook, never to a range in a closed workbook.

 

[Frank Kabel]

The UDF is named pull(), and it's in

http://www.google.com/[email protected]
..com

However, it can't be used as the first argument to OFFSET for the same
reason INDIRECT.EXT can't - it doesn't return a reference to a range
object. OFFSET's first argument can only ever be a reference to a
range in an open workbook, never to a range in a closed workbook.

Hi Harlan

thanks for the link.
Frank