Using ‘INDIRECT’ to refer to anohter file (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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:

=[Test.xls]Sheet1!A1

And if the workbook name and/or worksheet name contains spaces, the workbook/worksheet part must be surrounded by single quotes:

='[Test Book.xls]Sheet1'!A1

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

=INDIRECT("'["&A1&"]"&A2&"'!"&A3)

HTH

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote