reference different sheet in same workbook

[Eric]

Hi,
I've got a workbook with a number of worksheet tabs
labeled "May 2006", "June 2006", "July 2006", etc
in another tab called "Summary" I want to let the user enter a date and when
he does the date will be used to figure out witch tab and from that tab
display a block of data. (even better would be a dynamically updated drop
down box with a list of tabs that the user could choose from, but not
listing the first 3 worksheet tabs in the workbook as they are different
type.)
ie in "Summary" tab
Enter Date: <user enters date here, say "June 2006">
then when he presses enter, the formula goes and grabs block A4:L40
from the "June 2006" tab and displays it in Summary tab.
This way i can have lots of "Month tabs" and you can view any month
in the Summary sheet just by entering (or selecting) the desired date. Note
that each "Month sheet" also has a cell with that months date in it so that
could also be used to find the correct sheet.
How can i do this?
Thanks
Eric

 

[Dav]

create a drop down list of your dates in data validation in say cell C2
It needs to pick from a list of all your sheet names june 2006, july
2006 etc, which I have assumed below are dates

then in A4 put
=OFFSET(INDIRECT("'"&TEXT($c$2,"mmmm
yyyy")&"'!a1"),ROW()-1,COLUMN()-1)

then copy this formula to the cells to make your range to L40

if the dates in the validation are text

=OFFSET(INDIRECT("'"&$c$2&"'!a1"),ROW()-1,COLUMN()-1)

Regards

Dav

 

[Dave Peterson]

You have more responses at your other posts.

Hi,
I've got a workbook with a number of worksheet tabs
labeled "May 2006", "June 2006", "July 2006", etc
in another tab called "Summary" I want to let the user enter a date and when
he does the date will be used to figure out witch tab and from that tab
display a block of data. (even better would be a dynamically updated drop
down box with a list of tabs that the user could choose from, but not
listing the first 3 worksheet tabs in the workbook as they are different
type.)
ie in "Summary" tab
Enter Date: <user enters date here, say "June 2006">
then when he presses enter, the formula goes and grabs block A4:L40
from the "June 2006" tab and displays it in Summary tab.
This way i can have lots of "Month tabs" and you can view any month
in the Summary sheet just by entering (or selecting) the desired date. Note
that each "Month sheet" also has a cell with that months date in it so that
could also be used to find the correct sheet.
How can i do this?
Thanks
Eric