INDIRECT wrote...
In this case we are assuming A1 is in another workbook but in fact
A1 is in the same workbook and B7 is in workbook List.
Should I put

=INDIRECT(A1 & "[list]A1!B7") ?
....

?

Are you trying to access cell B7 in a worksheet named A1 in a workbook
named List in the drive/directory given in cell A1 in the same
worksheet into which you're entering this formula? If so, you can't
use INDIRECT because INDIRECT only works with *OPEN* files.

The only time you need drive/directory to identify workbooks in Excel
is when those workbooks AREN'T open. Since Excel can only open one
file at a time with a given base filename (e.g., you can open either C:
\foo\file.xls or C:\bar\file.xls but not both), that file is always
unambiguously identified by its base filename alone. In your example
above,

=INDIRECT("[list]A1!B7")

uniquely identifies the *ONLY* external reference INDIRECT could
return.

If you need to access a given cell or block of cells in several other
CLOSED files, you have to use something other than INDIRECT. See the
following archived thread for the alternatives.

http://www.google.com/groups?selm=hk...wsranger.c om