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