Reference to sheet name in another workbook
in [General]
|
From: simonc on 5 Jul 2008 06:07 I want to link cells a worksheet to cells in a worksheet in another workbook based on the tab name of the sheet in the other workbook. I have a master workbook with sheets for each month named Jan2008 Feb2008 etc. I want to compile a summary worksheet for a single month - say Jun2008 - where I want to extract some of the information (based on various criteria) from the master workbook sheet for Jun2008. I then want to be able to make a copy of this summary worksheet where I can simply put a different month in one cell and it will automatically get the information from the appropriate different sheet in the master workbook. (Does that make sense?) I know you can use CELL to get information about the tab name of a sheet into a cell, but I can't see how you can reference this to a different workbook. Grateful for assistance.
From: Bob Phillips on 5 Jul 2008 06:42 =INDIRECT("'["&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&".xls]Forums'!$A$2") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "simonc" <simonc(a)discussions.microsoft.com> wrote in message news:E8840AF7-0023-46A7-87BF-FB55930DD4CA(a)microsoft.com... >I want to link cells a worksheet to cells in a worksheet in another >workbook > based on the tab name of the sheet in the other workbook. > > I have a master workbook with sheets for each month named Jan2008 Feb2008 > etc. I want to compile a summary worksheet for a single month - say > Jun2008 - > where I want to extract some of the information (based on various > criteria) > from the master workbook sheet for Jun2008. I then want to be able to make > a > copy of this summary worksheet where I can simply put a different month in > one cell and it will automatically get the information from the > appropriate > different sheet in the master workbook. (Does that make sense?) > > I know you can use CELL to get information about the tab name of a sheet > into a cell, but I can't see how you can reference this to a different > workbook. > > Grateful for assistance.
From: simonc on 5 Jul 2008 15:03 Thanks for this which points me in the right direction. However, whenever I try to get INDIRECT to point to a cell in a different workbook I get the #REF! error. I do have the other workbook open. I'm using Excel 2000. Does that make a difference. "Bob Phillips" wrote: > =INDIRECT("'["&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&".xls]Forums'!$A$2") > > -- > HTH > > Bob > > (there's no email, no snail mail, but somewhere should be gmail in my addy) > > "simonc" <simonc(a)discussions.microsoft.com> wrote in message > news:E8840AF7-0023-46A7-87BF-FB55930DD4CA(a)microsoft.com... > >I want to link cells a worksheet to cells in a worksheet in another > >workbook > > based on the tab name of the sheet in the other workbook. > > > > I have a master workbook with sheets for each month named Jan2008 Feb2008 > > etc. I want to compile a summary worksheet for a single month - say > > Jun2008 - > > where I want to extract some of the information (based on various > > criteria) > > from the master workbook sheet for Jun2008. I then want to be able to make > > a > > copy of this summary worksheet where I can simply put a different month in > > one cell and it will automatically get the information from the > > appropriate > > different sheet in the master workbook. (Does that make sense?) > > > > I know you can use CELL to get information about the tab name of a sheet > > into a cell, but I can't see how you can reference this to a different > > workbook. > > > > Grateful for assistance. > > >
From: Bob Phillips on 6 Jul 2008 08:56 I have just tried it with Excel 2000, and it is working fine. What is the active sheet name and the workbook name, are they identical? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "simonc" <simonc(a)discussions.microsoft.com> wrote in message news:E7D26F15-4783-4F9C-A5D1-A9CA2F7FE84B(a)microsoft.com... > Thanks for this which points me in the right direction. However, whenever > I > try to get INDIRECT to point to a cell in a different workbook I get the > #REF! error. I do have the other workbook open. > > I'm using Excel 2000. Does that make a difference. > > "Bob Phillips" wrote: > >> =INDIRECT("'["&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&".xls]Forums'!$A$2") >> >> -- >> HTH >> >> Bob >> >> (there's no email, no snail mail, but somewhere should be gmail in my >> addy) >> >> "simonc" <simonc(a)discussions.microsoft.com> wrote in message >> news:E8840AF7-0023-46A7-87BF-FB55930DD4CA(a)microsoft.com... >> >I want to link cells a worksheet to cells in a worksheet in another >> >workbook >> > based on the tab name of the sheet in the other workbook. >> > >> > I have a master workbook with sheets for each month named Jan2008 >> > Feb2008 >> > etc. I want to compile a summary worksheet for a single month - say >> > Jun2008 - >> > where I want to extract some of the information (based on various >> > criteria) >> > from the master workbook sheet for Jun2008. I then want to be able to >> > make >> > a >> > copy of this summary worksheet where I can simply put a different month >> > in >> > one cell and it will automatically get the information from the >> > appropriate >> > different sheet in the master workbook. (Does that make sense?) >> > >> > I know you can use CELL to get information about the tab name of a >> > sheet >> > into a cell, but I can't see how you can reference this to a different >> > workbook. >> > >> > Grateful for assistance. >> >> >>
|
Pages: 1 Prev: restricted permission for worksheet Next: Excel 2007 slow changing between worksheets |