Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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") |
#6
|
|||
|
|||
Adding the single quotes did the trick. thanks for ou help
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
cell value as a worksheet reference | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |