NewAlgier wrote...
All of these three functions (offset, indirect, and match) seem to
work
only if the linked worksheet is open. Since Vlookup and Hlookup
work
on closed worksheets, I wonder if anybody has found a workaround
for
match and offset.
MATCH works with references into closed workbooks, at least as in
formulas like
=MATCH(A1,'C:\foo\Sheet1'!$A$1:$A$16,0)
OFFSET and INDIRECT only work for references into open workbooks
because both return range objects, and technically speaking, ranges
(as in Excel Range objects) exist *only* in *open* workbooks.
There's no easy replacement for OFFSET into closed workbooks.
Depending on what you're trying to do with the resulting array
(multiple cell references into closed workbooks are explicitly
returned as array, not as ranges that are implicitly converted into
arrays when used as arrays) there may be work-arounds.
As for INDIRECT, if you're trying to create dynamic references into
closed workbooks, see
http://groups-beta.google.com/group/...443753560f0075
(or
http://tinyurl.com/4ms2m )
Note that Google is apparenly trying to make the newsgroup archives
more difficult to reference. Gone is the simplicity of using a
messages Message-ID tag in the Google url. You'll now have to use
Google's own message ID.
---------
www.coffeecozy.com
Use your Bodum and give up cold coffee for good!