Using INDIRECT to refer to different workbooks

[Guest]

I am getting messed up on the grammar for this function. I am trying to
refer to a Worksheet called "125" - - which is on a different (opened)
Workbook.

How do I use the INDIRECT function so that it will go to a specific
Workbook, pick the correct '125' tab on that workbook, and return to me a
fixed cell value?

Thanks for your help. Excel Help is not clear about the grammar.

Thanks.

 

[Govind]

Hi,

Try

=INDIRECT("'[temp.xls]125'!"&"A1")

Replace the 'temp.xls' with the file name you need and replace the cell
A1 with the cell you need.

Note that Indirect works only when the source workbook is open.

Govind.

 

[Biff]

Hi!

You don't need to use Indirect unless you are using other cells to hold
references to the other workbook.

For example:

You want to return the value of cell A1 on Sheet!125 in workbook file_name.

You might do something like this:

A1 = file_name
B1 = 125

=INDIRECT("'["&A1&".xls]"&B1&"'!A1")

OR

=INDIRECT("'[file_name.xls]"&B1&"'!A1")

If you don't have references to the other wb in other cells, you could just
use this:

='[file_name.xls]125'!$A$1

The above can be used whether the other file is open or not. If it was
closed, you'd have to include the full path in the formula:

='C:\path\[file_name.xls]125'!$A$1

The big disadvantage to Indirect is that it requires the other file be open.

Biff

 

[Guest]

Thank you. Your example =INDIRECT("'[file_name.xls]"&B1&"'!A1") works just
fine.

I have two separate workbooks. I am making a matrix which will always refer
to the other workbook (so the first part of the address is fixed), but each
line of the matrix will refer to the same cell location in a number of
identical worksheets. Your answer gives me what I was looking for. Thanks
again.

Hi!

You don't need to use Indirect unless you are using other cells to hold
references to the other workbook.

For example:

You want to return the value of cell A1 on Sheet!125 in workbook file_name.

You might do something like this:

A1 = file_name
B1 = 125

=INDIRECT("'["&A1&".xls]"&B1&"'!A1")

OR

=INDIRECT("'[file_name.xls]"&B1&"'!A1")

If you don't have references to the other wb in other cells, you could just
use this:

='[file_name.xls]125'!$A$1

The above can be used whether the other file is open or not. If it was
closed, you'd have to include the full path in the formula:

='C:\path\[file_name.xls]125'!$A$1

The big disadvantage to Indirect is that it requires the other file be open.

Biff

I am getting messed up on the grammar for this function. I am trying to
refer to a Worksheet called "125" - - which is on a different (opened)
Workbook.

How do I use the INDIRECT function so that it will go to a specific
Workbook, pick the correct '125' tab on that workbook, and return to me a
fixed cell value?

Thanks for your help. Excel Help is not clear about the grammar.

Thanks.