Using a cell reference to refernce worksheet in another work book

  • Thread starter Thread starter dmoc2005
  • Start date Start date
D

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
 
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
 
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
Click to expand...
....

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

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