Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 6th 04, 11:27 PM
NewAlgier
 
Posts: n/a
Default Offset, indirect, match function limitation on linked worksheets.

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.

  #2   Report Post  
Old December 6th 04, 11:55 PM
hrlngrv - ExcelForums.com
 
Posts: n/a
Default

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!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Worksheets and Print Merge function pfe Excel Discussion (Misc queries) 2 December 2nd 04 11:23 PM
Linked worksheets John Kelly Excel Discussion (Misc queries) 1 December 2nd 04 12:36 AM
Find a Function to use accross different worksheets R. Hale Excel Worksheet Functions 3 November 25th 04 07:07 AM
Function to operate on ink linked to cell Alan T Excel Worksheet Functions 0 November 20th 04 10:03 PM
Indirect references in a linked formula Markshnier Excel Worksheet Functions 1 November 15th 04 02:49 AM


All times are GMT +1. The time now is 01:28 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017