reference different sheet in same workbook
 

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

reference different sheet in same workbook
 

the solution can better be provided if you put a sample of your file.

Eric Wrote:
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


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=563955

reference different sheet in same workbook
 

You could:

use datavalidation for your list of sheet names.


1.Define a name:
SheetList =
=TRANSPOSE(SUBSTITUTE(GET.WORKBOOK(1),"["& GET.DOCUMENT(88) & "]",""))

2. Select a (vertical) range for the sheetnames...
Enter an ARRAYformula by typing

=SheetList
then enter with ctrl-shift-return to achieve an array formula.
(note the range can be larger than the number of sheets,
you'll get some NA values at the end)

3.Define the next name
SheetValList =
=OFFSET(Sheet1!$A$8,3,0,COUNTIF(Sheet1!$A$8:$A$45, "*")-3)

4. Select a cell for your DV dropdown:
set the source of your data validation to =SheetValList
5. Name the input cell
e.g. SheetSelect

.... this took care of creating the input cell.

Create your summary range....
=INDIRECT(ADDRESS(ROW(B11),COLUMN(B11),,,SheetSele ct))
(this create an indirect formula that's easy to copy :)


HTH
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Eric wrote in m

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

reference different sheet in same workbook
 

keepITcool wrote:

You could:

use datavalidation for your list of sheet names.


1.Define a name:
SheetList =
=TRANSPOSE(SUBSTITUTE(GET.WORKBOOK(1),"["& GET.DOCUMENT(88) & "]",""))

2. Select a (vertical) range for the sheetnames...
Enter an ARRAYformula by typing

=SheetList
then enter with ctrl-shift-return to achieve an array formula.
(note the range can be larger than the number of sheets,
you'll get some NA values at the end)

3.Define the next name
SheetValList =
=OFFSET(Sheet1!$A$8,3,0,COUNTIF(Sheet1!$A$8:$A$45, "*")-3)

4. Select a cell for your DV dropdown:
set the source of your data validation to =SheetValList
5. Name the input cell
e.g. SheetSelect

... this took care of creating the input cell.

Create your summary range....
=INDIRECT(ADDRESS(ROW(B11),COLUMN(B11),,,SheetSele ct))
(this create an indirect formula that's easy to copy :)


HTH

I was ok till steps 4 & 5. Where do i "set the source of your data
validation to =SheetValList"? I dont understand what you mean there.
If i just enter "=SheetValList" in a cell then Excel pops up a dialog
wanting a file. SheetList is working , if i set up a few cells with
=SheetList i see the names of my worksheets :-) What is GET.DOCUMENT(88)?
Excel Help doesnt find GET, or DOCUMENT - why 88?
Thanks
Eric

reference different sheet in same workbook
 

starguy wrote:


the solution can better be provided if you put a sample of your file.

How? I'd be happy to, but its not a binary group (i dont think anyway)
But, do this:
1: new workbook - 3 tabs right?(sheet1 sheet2 sheet3)
2: on sheet2 and again on sheet3 create a block of text/formulas or
whatever, be sure there is some color and font stuff etc so its pretty.
3: On sheet1 place a combo box. The Combo items should be sheet2, sheet3
and every sheet name beyond those.
On sheet 1, users picks an item from combo box, sheet3 for example.
When he does that, then A1:M40 from sheet3 appears in sheet1 starting at
cell D10
Does that clarify it some?
See, if i have 128 sheets in this workbook then the user can view any sheet
in Sheet1 by picking one of the names (Sheet2-Sheet128) from the combo box
and view it on sheet1.
Just to clarify, i only want to view it, if an edit is needed to sheet 47
for example, then I'll run out to sheet 47 and edit it. But mostly this is
a quick way to view without having to endlessly scroll left or right to see
a particular sheet.
Thanks
Eric

reference different sheet in same workbook
 

Eric wrote in m

keepITcool wrote:


4. Select a cell for your DV dropdown:
set the source of your data validation to =SheetValList
5. Name the input cell
e.g. SheetSelect

... this took care of creating the input cell.

Create your summary range....
=INDIRECT(ADDRESS(ROW(B11),COLUMN(B11),,,SheetSele ct))
(this create an indirect formula that's easy to copy :)


HTH

I was ok till steps 4 & 5. Where do i "set the source of your data
validation to =SheetValList"? I dont understand what you mean there.
If i just enter "=SheetValList" in a cell then Excel pops up a dialog
wanting a file. SheetList is working , if i set up a few cells with
=SheetList i see the names of my worksheets :-) What is
GET.DOCUMENT(88)? Excel Help doesnt find GET, or DOCUMENT - why 88?
Thanks
Eric

Sorry. I (wrongly) assumed you knew what data validation was...

Data Validation will take care that your users cannot enter the wrong
sheet name and with following settings it provides a very convenient
incell dropdown.

4. .. the cell where the users will select the sheet
Via Data/Validation Tab Settings.
Allow: List
Source: =SheetValList

Uncheck Ignore Blank
Check In-Cell Dropdown.

(we'll ignore the other tabs, do those later)
Press ok.

You'll see a dropdown indicator to the right of the cell...
press that and you'll see the items in the range.

The Get.workbook() and get.document() functions are XLM commands.
XLM is the old macro language used before VBA. Documentation can be
downloaded from Microsoft. (excel XLM Function help)

Most of these dont work when called directly from a worksheet,
only on macro sheets.. OR via names :)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam