why does =INDIRECT need open sources ?

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
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
:)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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

:)
 
Upvote 0
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)).<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6><FONT COLOR=WHITE>Microsoft Excel - INDIRECT.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Tool(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>D5</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=3 BGCOLOR=White>=INDIRECT(C1)</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#33CCCC ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>A1:A3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT(C1)')><FONT FACE=MS P???? COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT("A1:A3")')><FONT FACE=MS P???? COLOR=#000000>1</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT(C1)')><FONT FACE=MS P???? COLOR=#000000>2</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=AVERAGE(INDIRECT(C1))')><FONT FACE=MS P???? COLOR=#000000>2</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT(C1)')><FONT FACE=MS P???? COLOR=#000000>#VALUE!</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT(C1)')><FONT FACE=MS P???? COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=6><U>Sheet1</U></TD></TR></TABLE><FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT><FONT COLOR=blue SIZE=1>The above image was automatically written by excel VBA.</FONT><FONT COLOR=blue SIZE=1>If you want this code,<A HREF=mailto:corosuke@chan.co.jp>click here</A> and I'll email the file to you.</FONT></CENTER>

Aladin
This message was edited by aladin akyurek on 2002-05-11 07:14
 
Upvote 0
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)).

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6><FONT COLOR=WHITE>Microsoft Excel - INDIRECT.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Tool(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>D5</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=3 BGCOLOR=White>=INDIRECT(C1)</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#33CCCC ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>A1:A3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT(C1)')><FONT FACE=MS P???? COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT("A1:A3")')><FONT FACE=MS P???? COLOR=#000000>1</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT(C1)')><FONT FACE=MS P???? COLOR=#000000>2</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=AVERAGE(INDIRECT(C1))')><FONT FACE=MS P???? COLOR=#000000>2</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT(C1)')><FONT FACE=MS P???? COLOR=#000000>#VALUE!</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=INDIRECT(C1)')><FONT FACE=MS P???? COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=6><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT>

<FONT COLOR=blue SIZE=1>The above image was automatically written by excel VBA. </FONT><FONT COLOR=blue SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and I'll email the file to you.</FONT>
</CENTER>

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.... ?
 
Upvote 0

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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top