Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: why does =INDIRECT need open sources ?

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  2. #2

    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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)
    ABCDE
    11 A1:A3:alert('=INDIRECT(C1)')>1:alert('=INDIRECT("A1:A3")')>1
    22 :alert('=INDIRECT(C1)')>2
    33 :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 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)).

    Microsoft Excel - INDIRECT.xls
    File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
    D5==INDIRECT(C1)
    ABCDE
    11 A1:A3:alert('=INDIRECT(C1)')>1:alert('=INDIRECT("A1:A3")')>1
    22 :alert('=INDIRECT(C1)')>2
    33 :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

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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default


    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •