Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using 'INDIRECT' to refer to anohter file (Excel 2000)

    Is it possible to use INDIRECT to refer to another workbook ? For example, cell A1 contains reference to another workbook. Cell A2 refers to A1, which then refers to that workbook. Something like : =INDIRECT(A1, Sheetname, cell address). Is this possible ? If so, what is the exact formula ? THanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using 'INDIRECT' to refer to anohter file (Excel 2000)

    You can refer indirectly to another workbook, but it MUST be open. If you try to refer to a closed workbook, you'll see the #REF error value.
    A reference to an open workbook looks like this:<pre>=[Test.xls]Sheet1!A1</pre>

    And if the workbook name and/or worksheet name contains spaces, the workbook/worksheet part must be surrounded by single quotes:<pre>='[Test Book.xls]Sheet1'!A1</pre>

    You must simulate this in the argument to INDIRECT, so you must either put the square brackets around the workbook name in cell A1, or add them in the formula.

    Let's say that A1 contains a workbook name such as Test.xls, A2 contains a worksheet name such as Sheet1, and A3 contains a cell address such as P37. The formula to retrieve the value from P37 would be<pre>=INDIRECT("'["&A1&"]"&A2&"'!"&A3)</pre>

    HTH

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Using 'INDIRECT' to refer to anohter file (Excel 2000)

    Just following up on Hans' comments about closed workbooks, if you need to use INDIRECT where the ultimate source is a closed workbook, you can avoid the #REF issue by having one or more intermediate worksheets in the target workbook directly referencing the required ranges in the source. You'd then only use INDIRECT to pull the data back from the intermediate worksheets.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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