what exactly prevents INDIRECT from accessing closed worksheets?

[Guest]

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.

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

z.entropic

 

[T. Valko]

what specifically prevents INDIRECT from accessing closed worksheets

Only MS knows for sure!

what would it take to enable this important functionality?

An act of God! People have been asking for this for years.


--
Biff
Microsoft Excel MVP

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.

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

z.entropic

 

[Harlan Grove]

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.

 

[Guest]

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.

My bad, it really appears that way, but it's not entirely true...

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

Thank you--and sorry for unjustly slandering your PULL function.

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


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.

Have you done any access speed comparison among INDIRECT.EXT, PULL and @@?
My main problems with INDIRECT.EXT show up if the closed worbooks are located
on a networked drive--it just becomes unbearably slow... When I have a few
minutes to spare, I'll try to do a head-to-head comparison with PULL's speed,
but then, network load varies all the time so the comparison may not be
valid...

Anyway, thank you for making the PULL function available to us, your (often
ascerbic ) help and the explanation.

z.entropic