"z.entropic" wrote...
What prompted my question is the slowness and finicky nature of
INDIRECT.EXT on one hand and the inability of Harlan Grove's PULL
function to deal with active cell addresses for copying formulas by
dragging on another. For example,
=PULL("'C:\[test.xls]Results'!A3")
will work, but will give the same A3 value for all the dragged and
copied cells, but
=PULL("'C:\[test.xls]Results'!"&A3) is NOT allowed.

OK, so you don't know how to search newsgroup archives effectively.

=PULL("'C:\[test.xls]Results'!"&CELL("Address",A3))

Fill it and/or copy & paste it wherever you want, and the A3 reference will
be treated as a relative reference (which it is).

So, what specifically prevents INDIRECT from accessing closed worksheets,
and what would it take to enable this important functionality?

It's purely syntactic. Excel's INDIRECT only returns references to
instantiative Range objects, i.e., ranges that could be instantiated as
objects of type Range in VBA. In Excel, those only exist in open workbooks.
FWIW, 123's @@ function, the rough equivalent of Excel's INDIRECT, quite
happily returns references to ranges in closed workbooks.

Nothing prevents Microsoft's Excel developers from changing this except
their own indifference to what a very small fraction of Excel users (to be
honest) believes they need.

Even if Excel's INDIRECT could refer into closed workbooks, it's unlikely to
differ significantly 123's @@'s performance - it'll still be VERY SLOW.