Reference external worksheet whose name is in a cell
 

We'd like to be able to get data from an external worksheet where the
filename is in a cell in the first sheet.

I.e. In File 'Master.xls', cell A1 contains the text 'TruckA' or
'TruckA.xls'. We want cell B1 to return the value from TruckA.xls, cell F2

If we knew the filename, we would write:
=[TruckA.xls]Sheet1!$F$2

But, we'd really like to have the external filename as a variable, so we
would expect to be able to write something like:
=["(A1.xls)"]Sheet1!$F$2
or
=["(A1)"]Sheet1!$F$2

Can this be done? (We are using Excel 2000)

Thanks!

Reference external worksheet whose name is in a cell
 

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

HTH

Die_Another_Day
Poly4 wrote:
We'd like to be able to get data from an external worksheet where the
filename is in a cell in the first sheet.

I.e. In File 'Master.xls', cell A1 contains the text 'TruckA' or
'TruckA.xls'. We want cell B1 to return the value from TruckA.xls, cell F2

If we knew the filename, we would write:
=[TruckA.xls]Sheet1!$F$2

But, we'd really like to have the external filename as a variable, so we
would expect to be able to write something like:
=["(A1.xls)"]Sheet1!$F$2
or
=["(A1)"]Sheet1!$F$2

Can this be done? (We are using Excel 2000)

Thanks!