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.

=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" wrote in message
...
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.

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" wrote in message
...
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.

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" wrote in message
...
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" wrote in message
...
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.