Reference to sheet name in another workbook

[simonc]

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.

 

[Bob Phillips]

=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]

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.

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

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.

 

[Bob Phillips]

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)

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.

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

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.