How to select a sheet and input data into certain cells

I have 36 sheets, sheet1 is my menu, also on sheet1 I created in colum

                  AB1=1stQTR  AC1=2ndQtr  AD1=3rdQTR  AE1=4thQT
AA2=2004    Sheet2          Sheet3              etc . . . 
AA3=2005    Sheet6          Sheet
AA4=2006    Sheet8          Sheet
AA5=2007    Sheet4          Sheet

I would like to be able to select a year and a quarter and it goes to the sheet, i.e
2005 2ndQtr goes to sheet5 and in a couple of cells input 2005 and 1st Quarter
I would also like to be able to print sheet5 and ask me if I want to print it agai
or close and upon closing go back to sheet

Is there an example that I might be able to look at that might lead me in th
direction that I need to go

Thank yo


0
anonymous (74722)
4/9/2004 7:56:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
469 Views

Similar Articles

[PageSpeed] 0

Hi
not really sure but for creating a list of sheets with hyperlinks see:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

--
Regards
Frank Kabel
Frankfurt, Germany


RK wrote:
> I have 36 sheets, sheet1 is my menu, also on sheet1 I created in
> column
>
>
>                   AB1=1stQTR  AC1=2ndQtr  AD1=3rdQTR  AE1=4thQTR
> AA2=2004    Sheet2          Sheet3              etc . . . .
> AA3=2005    Sheet6          Sheet5
> AA4=2006    Sheet8          Sheet6
> AA5=2007    Sheet4          Sheet7
>
> I would like to be able to select a year and a quarter and it goes to
> the sheet, i.e. 2005 2ndQtr goes to sheet5 and in a couple of cells
> input 2005 and 1st Quarter.
> I would also like to be able to print sheet5 and ask me if I want to
> print it again or close and upon closing go back to sheet1
>
> Is there an example that I might be able to look at that might lead
> me in the direction that I need to go?
>
> Thank you

0
frank.kabel (11126)
4/9/2004 8:02:11 AM
A GREAT site, but way over my head

Thank you
0
anonymous (74722)
4/9/2004 9:01:05 AM
Here is one way,

First select all of the data, including the year and qtr headings and name
that selection 'data' . To do this, select the data as I say, and in the
names box (the little dropdown box to the left of the formula bar) type
'data' (without the quotes).

Then create  year data validation in another cell, I use Z1. Select Z1, goto
menu Data>Data Validation. Change the 'Allow' dropdown to 'List', select the
'Source' edit box and then select your year headings (AA2:AAn where n is the
last row). DV will automatically change this to =$AA$2:$AA$n. Exit DV.

Then create a quarters data validation in say Z2.goto menu Data>Data
Validation. Change the 'Allow' dropdown to 'List', select the 'Source' edit
box and then select your quarters headings (AA1:Am1 where m is the last
column). DV will automatically change this to =$AA$1:$Am$1. Exit DV.

Now add this code into the worksheet module (right-click on the sheet tab,
select View Code from the menu, and paste the code in)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sSheet As String
    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("Z2")) Is Nothing Then
        With Target
            If .Value <> "" Then
                If Me.Range("Z1") <> "" Then
                    With Application
                        sSheet = .Index(Range("data"), _
                                .Match(Range("Z1"), Range("AA:AA"), 0), _
                                .Match(Range("Z2"), Range("AA1:AZ1"), 0))
                    End With
                    If sSheet <> "" Then
                        Worksheets(sSheet).Activate
                    End If
                End If
            End If
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

Quit the VBE and go back to the worksheet.

Now if you select Z1 you will see a dropdown box. Select your year from
this. Similarly, in Z2 you will see another dropdown box, select your
quarter from this. If all has worked okay, you should go to your target
sheet.

There is error checking, so if a sheet entry is blank, or the year or
quarter DV cells are blank nothing happens. This is how you can force it to
go to a sheet that is already selected. For example, Z1 = 2004, Z2 = 3rdQtr,
and that is where you want to go. Clear Z2, the re-select 3rdQtr from the
dropdown.


-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"RK" <anonymous@discussions.microsoft.com> wrote in message
news:B12CBBFB-3A50-4C16-A872-6E1782485C01@microsoft.com...
> A GREAT site, but way over my head.
>
> Thank you


0
bob.phillips1 (6510)
4/9/2004 11:42:47 AM
I selected the data, but it doesn't save "data" (without 
quotes) in the name box.

RK


>-----Original Message-----
>Here is one way,
>
>First select all of the data, including the year and qtr 
headings and name
>that selection 'data' . To do this, select the data as I 
say, and in the
>names box (the little dropdown box to the left of the 
formula bar) type
>'data' (without the quotes).
>
>Then create  year data validation in another cell, I use 
Z1. Select Z1, goto
>menu Data>Data Validation. Change the 'Allow' dropdown 
to 'List', select the
>'Source' edit box and then select your year headings 
(AA2:AAn where n is the
>last row). DV will automatically change this to 
=$AA$2:$AA$n. Exit DV.
>
>Then create a quarters data validation in say Z2.goto 
menu Data>Data
>Validation. Change the 'Allow' dropdown to 'List', 
select the 'Source' edit
>box and then select your quarters headings (AA1:Am1 
where m is the last
>column). DV will automatically change this to 
=$AA$1:$Am$1. Exit DV.
>
>Now add this code into the worksheet module (right-click 
on the sheet tab,
>select View Code from the menu, and paste the code in)
>Private Sub Worksheet_Change(ByVal Target As Range)
>Dim sSheet As String
>    On Error GoTo ws_exit:
>    Application.EnableEvents = False
>    If Not Intersect(Target, Me.Range("Z2")) Is Nothing 
Then
>        With Target
>            If .Value <> "" Then
>                If Me.Range("Z1") <> "" Then
>                    With Application
>                        sSheet = .Index(Range("data"), _
>                                .Match(Range("Z1"), Range
("AA:AA"), 0), _
>                                .Match(Range("Z2"), Range
("AA1:AZ1"), 0))
>                    End With
>                    If sSheet <> "" Then
>                        Worksheets(sSheet).Activate
>                    End If
>                End If
>            End If
>        End With
>    End If
>
>ws_exit:
>    Application.EnableEvents = True
>End Sub
>
>Quit the VBE and go back to the worksheet.
>
>Now if you select Z1 you will see a dropdown box. Select 
your year from
>this. Similarly, in Z2 you will see another dropdown 
box, select your
>quarter from this. If all has worked okay, you should go 
to your target
>sheet.
>
>There is error checking, so if a sheet entry is blank, 
or the year or
>quarter DV cells are blank nothing happens. This is how 
you can force it to
>go to a sheet that is already selected. For example, Z1 
= 2004, Z2 = 3rdQtr,
>and that is where you want to go. Clear Z2, the re-
select 3rdQtr from the
>dropdown.
>
>
>-- 
>
>HTH
>
>Bob Phillips
>    ... looking out across Poole Harbour to the Purbecks
>(remove nothere from the email address if mailing direct)
>
>"RK" <anonymous@discussions.microsoft.com> wrote in 
message
>news:B12CBBFB-3A50-4C16-A872-
6E1782485C01@microsoft.com...
>> A GREAT site, but way over my head.
>>
>> Thank you
>
>
>.
>
0
anonymous (74722)
4/12/2004 10:38:29 PM
Reply:

Similar Artilces:

exporting/linking data from a row on one sheet into another sheet
I have a master sheet with all the sales information that my company tracks. I would like to create seperate sheets for each sales rep. I have all their initials listed under one column. Can I take each row and put the information onto another sheet based on what I put into the initials colum? exporting/linking data from a row on one sheet into another sheet Hi Clark see responses in Excel.worksheet.functions NG Frank Clark Haddock wrote: > I have a master sheet with all the sales information that > my company tracks. I would like to create seperate > sheets for each sa...

Can I have a CRichEdit not select all text upon WM_SETFOCUS ?
Hy I have an CRichEdit derived class that implements a commands console. I would like the CRichEdit to stop selecting all text when I press Tab (because there is no other control in the dialog but a menu and the CRichEdit) and when some other pop-up window is opend by the parent dialog. Thank you "Timothy Madden" Romania "Timothy Madden" <batman@rmv.spam.home.ro> wrote in message news:3118d8F36ak0sU1@uni-berlin.de... > Hy > > I have an CRichEdit derived class that implements a commands console. > I would like the CRichEdit to stop selecting all text w...

Right Click to Autofill cells in 2003
Hello All, In 97 to 2002 it was possible to right click when using Autofill to copy the cells down rather than continuing the sequence. in 2003 i just get a pretty red line (what purpose does this serve?). Does anyone know if I can get back the old functionality? Many thanks, Danny I have that functionality in Excel 2003 as well, maybe you have installed an add-in? In any case if you hold down ctrl while using the left click copy down it will also copy as opposed to fill a series Regards, peo sjoblom "DannyJ" wrote: > Hello All, > > In 97 to 2002 it was possi...

Changing font on a protected sheet #2
I meant to say is that in the 2000 version. I went to TOOLS>PROTECT>PROTECT SHEET and did not see a check box to allow formatting. Any ideas??? Thanks... yep. Kate wrote: > > Is that in the XP version??? > > >-----Original Message----- > >Kate" <anonymous@discussions.microsoft.com> wrote in > message > >news:48e701c4a70b$a11d0910$a501280a@phx.gbl... > >> Hi, > >> > >> I have a worksheet that I need to have protected so that > >> the user can make changes only in certain cells. > >> > >...

How to sort multiple sheets (which have same format) at one time?
I have a workbook with multiple sheets which have the same format. I need to sort all in the same manner. How can this be done. I noticed that the sort function is not available when selecting/grouping multiple sheets. I'm going to take you at your word and make a few assumptions. Taking you at your word: ALL sheets in the workbook need to be sorted. Assumptions: only 1 column is used to determine the sort order, and the sort is to be in ascending order. The following code will do that, and allows you to define which columns are included in the sort, and which column ...

How do I randomly shuffle the cells in a column?
In Excel 2003, I have a column, say a1-a10, with certain entries. I need to create another column, b1-b10, with the same entries assigned randomly (shuffled) to the cells. Just one way .. Put in B1: =INDEX(A:A,RANK(C1,$C$1:$C$10)) Put in C1: =RAND() Select B1:C1, copy down to C10 B1:B10 returns a random shuffle of what's in A1:A10 Press F9 to re-shuffle -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Jason D" <Jason D@discussions.microsoft.com> wrote in message news:36244977-7EE5-436B-8617-59C15310B080@microsoft.com... >...

How to change cell shading/fill based on value in another cell?
I have a sheet with two columns of experimental data. Let's say the data is in F10:F25 (column 1) & G10:G25 (column 2). There is a value in another cell (A1) that contains a 1 or a 2. If A1=1, I want to turn shading in column 1 agree and in column 2 off. If A1=2, I want to reverse that. If there a worksheet function that will do that? I was hoping for something like: =cellshading(range,color) I could then execute one of these two statements: If A1=1 then cellshading(F10:F25,"Green") cellshading(G10:G25,"None") Else cellshading(G10:G25,"Green&...

Excel 97-remove link but keep data
Hi guys, I have a pre-existing excel 97 s/s which has links to other s/s's. I would like to keep the data only (like paste-special, values) and remove the linked reference from showing in the current s/s. Any ideas how to do this instead of me changing each linked reference? Thanks in advance! search for .xls or [ or ] to see if you can find the formulas that contain links to other workbooks. Van wrote: > > Hi guys, > > I have a pre-existing excel 97 s/s which has links to other s/s's. I would > like to keep the data only (like paste-special, values) and rem...

Using Excel 2000 as Data source for Word 2000 document
Problem using Mail Merge using Word 2000 & Excel 2000. After numerous problems - Word failed to connect to Data Souce file then it would & then it wouldn't and so on - reasons which are unknown! The 106 records in 19 fields from the Excel Data Source are only transferring the information for the first 104 records. I am looking to add further records but obviously need to overcome this problem. Hi Malcolm- Obvious, perhaps, but are you certain that the records are in consecutive rows & no vacant columns separating the fields of data? Does the data range contain any Merge...

How to get SUMPRODUCT on filtered cells
I'm currently using the formula =SUMPRODUCT((F2:I1475>=1)*(F2:I1475<=9999)) This gives me the number of the full range of cells but I need this to update whenever I use a variety of filters. Any help is greatly appreciated Sam.D Say we have data in A1 thru B29 like: flia value 0 pass 2 pass 1 pass 2 pass 1 pass 2 pass 0 pass 2 pass 1 pass 2 pass 1 pass 0 pass 2 pass 0 pass 1 pass 1 fail 1 fail 0 fail 2 fail 1 fail 0 fail 1 fail 2 fail 1 fail 1 fail 1 fail 1 fail 2 fail and are appling autofilter to column a. We want to use sumpr...

Sum Multiple Worksheets, same named cell
I have multiple worksheets containing the same named cell (e.g. Month_Total). On a totals worksheet, I am not able to use =SUM('START,END'!Month_Total) because of the local/global naming. Is there a way around this? (Month_Total will not always be in the same cell on each worksheet). Thanks! It seems to me that you have two options. 1. Name each of your "Monthly Total"s slightly different. OR 2. Do not use the cell range naming. "uncreative" <uncreative@discussions.microsoft.com> wrote in message news:60A150C6-50BC-4876-A31F-1C...

How to group all data from 1 column into several columns (Redo)
I have 2 columns in my spreadsheet, Company Names and Event. The Event column values are Event 1, Event 2, Event 3, Event 4 and the company names repeat for each of the events that they attended. So sometimes I might have a company listed 4 times with each Event corresponding to it in the next column. How can I consolidate all of the company names so that there is 1 row for each company and all events are on the same row but in different columns marked with an x (the column labels will be Company name, Event 1, Event 2, Event 3, Event 4). This is what I have... Company Name...

Problem of DGV multi-row selection
Hi everyone I have a problem of DataGridView's multi-row selection behavior. When holding the ctrl key down and DGV's selecttionmode had set to FullRowSelect, user could select multiple rows by click on those rows. And, when user click on a row which already had been selected the row would been deselected. The problem is this select/deselect is based on "mouse down" event instead of "mouse click" event, I mean even before user release the left key the row already had been selected/deselected, which is not an ideal behavior to initialize a drag dro...

Many to Many Data Entry
Hi all, This is a doozy of a problem, using a form to join many to many fields, so thanks if you're up for checking it out! I have two tables that are linked with a many to many relationship using a third table. TblZones (with fields ZoneNo(primary key, autonumber), ZoneMo, Zone Description, CustNo, SiteNo, etc) TblDevices (with fields DeviceNo (primary key, autonumber), DeviceType, Location, CustNo, SiteNo, etc) TblDeviceZoning (With fields ID (primary key, autonumber), DeviceNo, and ZoneNo) A device may only be assigned to a zone that shares the same customer number...

Data sheet populating into multiple sheets
I'm looking to build a spreadsheet with one main page that can be populated with data and automatically pull that data over to other more specific sheets. For example, If I have sales on the main sheet and have check boxes for more specific sales on the same sheet (i.e. liquor sales, food sales etc..) I want that number to poulate on the specific sales sheet. I'm assumming I have to wite an IF/Then Statement to say if an "x" is put into a cell then copy and past the data in the specified cells within that row and pull that information over to sheet Y. One respon...

Overlay 4 years of data as a line on 4 years of columns for several x category labels
Dear Charting aficianados, The tutorial at http://peltiertech.com/Excel/Charts/ClusterColAndLine.html is the closest example I can find to a charting conundrum I am facing, though what I am hoping to achieve is still a little different. I've made a mock up of what I'm hoping to achieve - though I have had to use drawing objects for the line series, to overlay on the columns - I'm sure there is a way to get excel to do this - do you think there is? (sheet named mock-up) A workbook with mocuk-up and my other charting attmempts can be found: http://www.savefile.com/files/2690840 T...

Algebra within a cell
How do you set-up a formula in a cell that multiplies a constant times the number you insert? Ex. the constant is .315 remains present at all times only the number you insert changes - =.315*(x) You can't unless you use an event macro, if you need a formula you have to use another cell as help =0.315*A2 where A2 holds x you can also put 0.315 in a cell, copy it, select the cel with x and then paste special and select multiply. But to get this instantly you have to use something like Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("A2"), Target)...

Displaying Multiple Cell Information in Single Cell
Hi all, I'm trying to present (text) data from multiple cells in another workbook in a single cell. I've tried the following formula but it returns a #VALUE! in my destination cell: ='[Project1.xls]Dependencies'!$A$6, '[Project1.xls]Dependencies'!$A$7 Any ideas on if this is possible? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501 View this thread: http://www.excelforum.com/showthread.php?threadid=503954 Use the "&...

Tidying up Office Data Files
My Office Data files are in a mess. I have discovered that I have Outlook (approx 0.8GB) and Outlook1 (approx 0.8GB) Mailbox (32KB) and Archive (0.35GB). I created Archive some time ago but have not archived recently. I don't know why I have 2 Outlook files - possibly created when I transferred data to a new laptop a few weeks ago. I would like to re-combine everything into 1 file so that I can start again and set up a proper Archive system again. How can I do this without risking losing files or creating duplicates or triplicates. As you will note the files are very big! Any h...

Formatting cells
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel All of a sudden when I try to format a column of or an individual cell (format as a number, no decimal places, comma for thousands) only 2 of the four numbers appear in the cell. When I double click to view what is actually in the cell I see the four numbers I entered, the first two separated by the last two with a decimal. <br> I have tried this on new sheets and workbooks and the same thing happens. I can't tell for sure, but try going to Excel>Preferences> Edit. If there's a check on '...

How do I add multiple comments to a cell?
I need to add more than one comment to data in a single cell, is this possible? Not possible. deberjones wrote: > > I need to add more than one comment to data in a single cell, is this possible? -- Dave Peterson ...

Extract Data fromLotus notes
Hi I am using an ADODB Recordset to open a Lotus Notes dataset, executing the following SQL string: stSQL = "SELECT OptionMR.ClientName, OptionMR.ClientID, OptionMR.Status FROM OptionMR OptionMR WHERE (OptionMR.ClientID='6911')" and can sucessfully return data, using the following loops: While rst.EOF = False For icols = 0 To rst.Fields.Count - 1 ActiveCell.Value = rst.Fields(icols).Value ActiveCell.Offset(0, 1).Range("A1").Select Next ' On Error Resume Next rst.MoveNext 'On Error GoT...

No data in dynamic spreadsheet
Hi One of our users is using the MS CRM 3.0 web client and terminal services. When this users exports a dynamic excel spreadsheet and enables automatic refresh no data is visible in the spreadsheet. The user is using Office 2003 and is able to view all records in CRM so the permissions are correct. Anyone have an idea as to what could be causing this? Static excel spreadsheets display data. Does the user need to connect to the SQL database in some way? Appreciate your assistance. Thanks Mark When the spreadsheet is opened, check if there is a message at the top asking to unlock the ...

Font problem in property Sheet
I am working on a project, in whihc i am using the propertysheet. I am using bitmap(bilbilt) as backgrpound in each of dialog of property sheet. When i am running the application in English US OS it is working fine but in case of japanese OS the back groud image(bitmap)is not coming correctlty. I think this is font problem, Because default font is change from US OS to Japanese. Because of font change dialog width is increasing and height is decreasing. How we can stop to increase in Width and Decrease in height. Essentially, while you can, it is a Really Bad Idea. Using bitmaps as backgr...

Sheets Tabs
Is there a way to change the location of the sheets tabs? Instead of having them at the bottom of the worksheet, I would need to have them vertically, next to the row number. I know it is kind of a weird question, but it would simplify my job :-) Thanks in advance. Regards, Emece.- NO -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Emece" <Emece@discussions.microsoft.com> wrote in message news:DF6FDE2F-6513-44CB-ADE4-99F57A8CE7E9@microsoft.com... > Is there a way to change the location of the sheets tabs? Instead of >...