Using a cell reference to refernce worksheet in another work book
 

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

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

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

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

" wrote:

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 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")

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