why does =INDIRECT need open sources ?

[Chris Davison]

Morning all,

Why does =INDIRECT require any source workbooks to be open ?

My basic understanding of it is that it looks in a specified target and brings back the value referred to by the criteria

fair enough....

But then isn't this exactly what =VLOOKUP does also potentially ? It has to go to an external scource, sometimes, and bring back a value based on the criteria

I know they do different jobs, but in terms of having to access external workbooks, search for a desired target and bring back info based on criteria, aren't they doing essentially the same kind of thing..... why does one therefore work with closed workbooks and the other does not ?

many thanks
Chris

[Lo Lun To]

The Help file re INDIRECT says :-

"If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value."

Perhaps Microsoft can explain why it is like this.

[Chris Davison]

thanks....

yes, I'm aware of it's limitations, I'm asking people at this board to see if they have any comments before I try Microsoft themselves

[Aladin Akyurek]

Why does =INDIRECT require any source workbooks to be open ?

My basic understanding of it is that it looks in a specified target and brings back the value referred to by the criteria

Not exactly. INDIRECT is a kind of dereferencer. It "evaluates" a single cell or multicell ref. The function, being volatile, needs a recalc before it can dereference its ref arg. It's more apparent in cases like:

=INDIRECT(ADDRESS(...)&":"&ADDRESS(...))

fair enough....

But then isn't this exactly what =VLOOKUP does also potentially ? It has to go to an external scource, sometimes, and bring back a value based on the criteria

INDIRECT must sometimes return a constant array that results from dereferencing a multicell ref. That should be difficult to assess/determine beforehand.

I know they do different jobs, but in terms of having to access external workbooks, search for a desired target and bring back info based on criteria, aren't they doing essentially the same kind of thing..... why does one therefore work with closed workbooks and the other does not ?

If my hypothesis is right about INDIRECT requiring a recalc, then it's obvious that the WB must be open for INDIRECT to work for when the WB is opened, a recalc is then done.

See the figure (where the formula in D5 is array-entered, something HtmlMaker cannot render (yet)).

Microsoft Excel - INDIRECT.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
D5		=	=INDIRECT(C1)
*	A	B	C	D	E
1	1	*	A1:A3	:alert('=INDIRECT(C1)')>1	:alert('=INDIRECT("A1:A3")')>1
2	2	*	*	:alert('=INDIRECT(C1)')>2	*
3	3	*	*	:alert('=AVERAGE(INDIRECT(C1))')>2	*
4	*	*	*	:alert('=INDIRECT(C1)')>#VALUE!	*
5	*	*	*	:alert('=INDIRECT(C1)')>1	*
Sheet1

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.


Aladin

[ This Message was edited by: aladin akyurek on 2002-05-11 07:14 ]

[Chris Davison]

On 2002-05-11 04:23, Aladin Akyurek wrote:
Why does =INDIRECT require any source workbooks to be open ?

My basic understanding of it is that it looks in a specified target and brings back the value referred to by the criteria

Not exactly. INDIRECT is a kind of deferencer. It "evaluates" a single cell or multicell ref. The function, being volatile, needs a recalc before it can deference its ref arg. It's more apparent in cases like:

=INDIRECT(ADDRESS(...)&":"&ADDRESS(...))

fair enough....

But then isn't this exactly what =VLOOKUP does also potentially ? It has to go to an external scource, sometimes, and bring back a value based on the criteria

INDIRECT must sometimes return a constant array that results from deferencing a multicell ref. That should be difficult to assess/determine beforehand.

I know they do different jobs, but in terms of having to access external workbooks, search for a desired target and bring back info based on criteria, aren't they doing essentially the same kind of thing..... why does one therefore work with closed workbooks and the other does not ?

If my hypothesis is right about INDIRECT requiring a recalc, then it's obvious that the WB must be open for INDIRECT to work for when the WB is opened, a recalc is then done.

See the figure (where the formula in D5 is array-entered, something HtmlMaker cannot render (yet)).


You can see the formula of cells only click each above hyperlinks

The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.


Aladin

ahhh

so whilst we cannot have VLOOKUP return an array of values, it never needs to recalc in order to check itself beforehand, hence does not need to open a source workbook.... ?

[Aladin Akyurek]

ahhh

so whilst we cannot have VLOOKUP return an array of values, it never needs to recalc in order to check itself beforehand, hence does not need to open a source workbook.... ?


As far as VLOOKUP is concerned, the result to retrieve is fixed upon closing the target WB. Not so for INDIRECT and OFFSET (I didn't check the behavior of this one). They need to dereference their ref arg if these are in a different WB.

Aladin