Using a cell reference to refernce worksheet in another work book

[dmoc2005]

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

 

[Peo Sjoblom]

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

 

[dmoc2005]

All the workbooks are open when I am doing this. It is giving me a
syntax error when I try to use a cell name. If I change the A5 to the
actual name of the worksheet "company 1" then it works

 

[Peo Sjoblom]

OK, if both workbooks are open then first of all you won't need the path so
this should be enough

=INDIRECT("[HISTORICALWORKSHEET2.xls]"&A5&"!$U$6")

note that it will retrun a ref error when the workbook is closed and the
link I posted have some solutions for that

Regards,

Peo Sjoblom

 

[hrlngrv]

Peo Sjoblom wrote...

OK, if both workbooks are open then first of all you won't need the path so
this should be enough

=INDIRECT("[HISTORICALWORKSHEET2.xls]"&A5&"!$U$6")

note that it will retrun a ref error when the workbook is closed and the
link I posted have some solutions for that

....

Safer always to include single quotes around the workbook and worksheet
names.

=INDIRECT("'[HISTORICALWORKSHEET2.xls]"&A5&"'!$U$6")

 

[dmoc2005]

Adding the single quotes did the trick. thanks for ou help