You can only use a built in function (INDIRECT) when all workbooks in
question are open, there are some workarounds

http://tinyurl.com/3nemj


Regards


Regards,

Peo Sjoblom

" wrote:

I have 2 spreadsheets setup book1 and book2. Book1 contains multiple
rows of data for different companies (50 rows). Book2 contains some
historical data on those companies. Book2 is broken down into 1
worksheet per company for a total of 50 worksheets. I want to create a
formula on book1 that will pull the specified data from the
corresponding worksheet in book 2. In book 1 worksheet there is a field
that has the worksheet name in book 2 to pull the data from. I have
tried X:\companies\[HISTORICALWORKSHEET2.xls]A5'!$U$6

Where A5 is the cell reference containg the worksheet name.

Does anyone know if this is possible and if so the correct syntax.
Thanks in advance

Denis