Simple macro help #2

I have a workbook with two sheets in it. the workbook is used to keep track 
of machines in a particular location. on one sheet there is a weekly update 
and the second sheet is a year to date tracker. what i'm trying to do is on 
the weekly sheet i want to update the numbers for each location, weekly 
obviously. on the other sheet i want it to keep track of the yearly amount. 
for example on the weekly sheet i have 3 machines in memphis and 4 in los 
angeles. the yearly sheet will read 3 for memphis and 4 for los angeles. then 
at the end of week two i update the weekly sheet saying there were 5 machines 
in memphis for that week and 1 in los angeles.  the yearly will then 
automatically update and say for the year there are 8 in memphis and 5 in los 
angeles...this will keep going on thru out the whole year. surely there is a 
macro for this, but what is it? thank you!
0
chippyp (9)
1/10/2006 5:22:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
448 Views

Similar Articles

[PageSpeed] 19

Do you maintain the weekly figures in separate columns? If so a formula will
do it.

=SUMPRODUCT((Sheet2!A1:A20="memphis")*(Sheet2!B1:M20))

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"chip_pyp" <chippyp@discussions.microsoft.com> wrote in message
news:1CDFFDC1-4141-4D14-8DF2-94806BF8C094@microsoft.com...
> I have a workbook with two sheets in it. the workbook is used to keep
track
> of machines in a particular location. on one sheet there is a weekly
update
> and the second sheet is a year to date tracker. what i'm trying to do is
on
> the weekly sheet i want to update the numbers for each location, weekly
> obviously. on the other sheet i want it to keep track of the yearly
amount.
> for example on the weekly sheet i have 3 machines in memphis and 4 in los
> angeles. the yearly sheet will read 3 for memphis and 4 for los angeles.
then
> at the end of week two i update the weekly sheet saying there were 5
machines
> in memphis for that week and 1 in los angeles.  the yearly will then
> automatically update and say for the year there are 8 in memphis and 5 in
los
> angeles...this will keep going on thru out the whole year. surely there is
a
> macro for this, but what is it? thank you!


0
bob.phillips1 (6510)
1/10/2006 6:08:44 PM
Unfortuantely not. on the weekly spreadsheet i have memphis in c3 then 
jackson in c4 and so on with about 13 other locations. there is only one spot 
for the weekly figures so i wanted the yearly sheet to pull the number off 
the weekly and add it to a stored number that way it is always accumulating 
as the weekly is updating. 

"Bob Phillips" wrote:

> Do you maintain the weekly figures in separate columns? If so a formula will
> do it.
> 
> =SUMPRODUCT((Sheet2!A1:A20="memphis")*(Sheet2!B1:M20))
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "chip_pyp" <chippyp@discussions.microsoft.com> wrote in message
> news:1CDFFDC1-4141-4D14-8DF2-94806BF8C094@microsoft.com...
> > I have a workbook with two sheets in it. the workbook is used to keep
> track
> > of machines in a particular location. on one sheet there is a weekly
> update
> > and the second sheet is a year to date tracker. what i'm trying to do is
> on
> > the weekly sheet i want to update the numbers for each location, weekly
> > obviously. on the other sheet i want it to keep track of the yearly
> amount.
> > for example on the weekly sheet i have 3 machines in memphis and 4 in los
> > angeles. the yearly sheet will read 3 for memphis and 4 for los angeles.
> then
> > at the end of week two i update the weekly sheet saying there were 5
> machines
> > in memphis for that week and 1 in los angeles.  the yearly will then
> > automatically update and say for the year there are 8 in memphis and 5 in
> los
> > angeles...this will keep going on thru out the whole year. surely there is
> a
> > macro for this, but what is it? thank you!
> 
> 
> 
0
chippyp (9)
1/10/2006 6:23:09 PM
Thought that might be the case.

Try this VBA solution

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B200"
Dim iPos As Long

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            On Error Resume Next
            iPos = Application.Match(.Offset(0, -1).Value,
Worksheets("Sheet3").Range("A:A"), 0)
            On Error GoTo 0
            If iPos > 0 Then
                Worksheets("Sheet3").Range("B" & iPos).Value = _
                    Worksheets("Sheet3").Range("B" & iPos) + .Value
            End If
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"chip_pyp" <chippyp@discussions.microsoft.com> wrote in message
news:47B68D33-8A24-4F77-8A34-EF5B7B0E12E7@microsoft.com...
> Unfortuantely not. on the weekly spreadsheet i have memphis in c3 then
> jackson in c4 and so on with about 13 other locations. there is only one
spot
> for the weekly figures so i wanted the yearly sheet to pull the number off
> the weekly and add it to a stored number that way it is always
accumulating
> as the weekly is updating.
>
> "Bob Phillips" wrote:
>
> > Do you maintain the weekly figures in separate columns? If so a formula
will
> > do it.
> >
> > =SUMPRODUCT((Sheet2!A1:A20="memphis")*(Sheet2!B1:M20))
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "chip_pyp" <chippyp@discussions.microsoft.com> wrote in message
> > news:1CDFFDC1-4141-4D14-8DF2-94806BF8C094@microsoft.com...
> > > I have a workbook with two sheets in it. the workbook is used to keep
> > track
> > > of machines in a particular location. on one sheet there is a weekly
> > update
> > > and the second sheet is a year to date tracker. what i'm trying to do
is
> > on
> > > the weekly sheet i want to update the numbers for each location,
weekly
> > > obviously. on the other sheet i want it to keep track of the yearly
> > amount.
> > > for example on the weekly sheet i have 3 machines in memphis and 4 in
los
> > > angeles. the yearly sheet will read 3 for memphis and 4 for los
angeles.
> > then
> > > at the end of week two i update the weekly sheet saying there were 5
> > machines
> > > in memphis for that week and 1 in los angeles.  the yearly will then
> > > automatically update and say for the year there are 8 in memphis and 5
in
> > los
> > > angeles...this will keep going on thru out the whole year. surely
there is
> > a
> > > macro for this, but what is it? thank you!
> >
> >
> >


0
bob.phillips1 (6510)
1/10/2006 7:57:56 PM
Reply:

Similar Artilces:

Unable to see Tasks in PWA when 2 or More Users Assigned to Task
We are using MS Project Server Professional 2007 (and PWA) SP1 We have been using a project for some time with no issues, however, recently when a task has two users assigned, these users are unable to see the task in the My Tasks list in PWA(although it shows correctly in Professional). It seems that this is happening when a second user is assigned to an already existing task. We have not yet narrowed this down to just being one project, but other projects on the server do not seem to experience this problem. We have not made any permission changes or any other setting changes. ...

Multiple Relationships between 2 Entities
All. We've encountered a problem where pairs of entities with 2 (or more) relationships between them cause issues when creating records through the associated grids. We have an entity with 2 relationships to Account, there are 2 navbar items displayed on the Account (this is fine as the 2 relationships are for different purposes) however creating a record from the associated grid results in both lookups to the Account being set to the same account. Checking the relationship mappings, both relationships have both lookups mapped to account.accountid (which is not what we want) and a...

Help! on Creating Auto Response
Hi. I'm a newbie. Where I work I need to send several emails in a day reminding the dept to bring me a workorder(papers) so that my dept can print jobs. Is there a way when I select the persons email address, that an auto email continues to be sent, let's say every 1/2 until I receive the paper work. Once I receive the paper work, I have the option to shut it off. Is this possible or is there a 3rd party software that can do this. TIA. See if the information on the following page helps: http://www.slipstick.com/dev/olforms/skedrpt.htm -- Jocelyn Fiorello MVP - Outlook *** Replie...

mortgage calculator #2
I wouls like to be able to work out my monthly mortgage payment (interest only) by inputing the following details amount of loan ( figure to entered ?) term of loan (in years 1 to 25) interest rate ( current figure to be entered ie 4.75) My monthly repayment is ( display answer) Thanks for your help Julian Hmm...it sounds like the function IPMT function. Take a look at that. HTH -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7094 View this thread: http://ww...

Sending/Receiving #2
I just installed Microsoft Office Enterprise 2007 on my computer and trying to set up Outlook. When I tested the settings, it said everything was fine. Yet, when I hit send/receive, I get the message, "The Operation Failed. An object could not be found." I don't understand this and tried looking at the microsoft website and find nothing about this. When I tried to send an individual email, it says that it failed because I didn't have enough memory. When I ran a memory diagnostics test, it said everything was fine. I have been running Windows Mail on the computer...

Extra W-2 form will all zeroes
What would cause a second W-2 form to print for an employee? This extra W-2 prints the Company name, address and EIN and the employee's name, address and social security number. All the other boxes are either blank or zeroes. Do we need to delete a record somewhere? I'm surprised a W-2 would actually print if everything is zero. Does this record show up on the payroll validation report? Does this employee exist under more than one Employee ID? "Elaine" wrote: > What would cause a second W-2 form to print for an employee? > > This ext...

Simple hack to get $500 to your home.
Simple hack to get $500 to your home at http://uknews.tk Due to high security risks,i have hidden the cheque link in an image. in that website on left side below search box, click on image and enter your name and address where you want to receive your cheque.please dont tell to anyone. ...

Marking deleted items as read #2
Hi, Does anyone know if there is a rule combination that will mark messages that I delete as read. When I delete stuff it really bugs me that it stills marks the items as unread. I don't care! Thats why I deleted them! If anyone knows how to fix this I'd greatly appreciate it Many thanks Simon Simon, My first question would be, if you are deleting unread messages, why would you be concerned if they are marked "read" or "unread"?? Just empty the deleted items folder and be done with it. If there is a concern, then go into the deleted items folder and selec...

Deleted Exchange accounts reappear #2
We have an infrequent issue where Exchange 2000 and 2003 mailboxes sometimes reappear after being deleted. (Some of the users accounts are NT based, and some are AD.) Has anyone else experienced this? If so, is there a remedy. ...

AutoFit #2
Hi When I use the AutoFit function in my VB Application, to format a Excelsheet the numbers in the column formats. From being 230,1 to 230,144444444. Why? Thanks in advance /Sara My guess is your number is really 230,144444444. You could format the number to show fewer decimals or even use a worksheet function to round it. =round(a1,3) for example. But if your cell is formatted as General and you have that number in it, then if you shrink the columnwidth, excel will adjust the display (but not the underlying value). Widen it using autofit (manually or in code) will show all the digits....

splash screen #2
I am using a introductory splash screen for my application. It uses a timer to show the splash and it disappears after 3 seconds. However, I want it to synchronize with the application so that it spends as much time on the screen as it needs to. For example, if the application loads in 5 seconds on a slower computer, it should stay visible for exactly 5 seconds. How can this be done? Currently, I am using this splash screen from codeproject: http://www.codeproject.com/dialog/dialogspl.asp Thanks. Jess On Sun, 12 Mar 2006 03:05:47 GMT, "Jessica Weiner" <jessica@gmail.co...

can i import a database from access into visio #2
im want to import a database from access to visio in hopes it will draw my er digram for me. any syggestions? ...

converting 1.x database to 2.0 from DOS files
I saved the 1.x database on floppies. Now I need the database again and need to upgrade it. How do I copy the floppies with the 1.x database so that it will work with access 2.0 in an XP pro environment? ...

Access 2.0 to Access 97 10-07-04
I would like to convert a db from Access 2.0 on one pc to Access 97 on another pc on my network. I'm new to access, so any input you can give will be greatly appreciated. Thanks. Hi, My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups. To convert an Access 2.0 database to 97, in Access 97 go to Tools, Convert Database on the menu bar. Select the Access 2.0 database to convert. If you have errors converting, try importing the objects instead of converting (File, Get External Data, Import). Please note that you might have code that needs to be updated or...

Hide Formula Results #2
Here I go again. LOL! I know this probably has a very simple solution and I am just not able to make myself clear on what it is I'm trying to accomplish. Think checkbook scenario. Okay, this is basically it. What I want is for it not to show the total all the way down if there have been no debits or credits posted on that row. A B C D E 1 2 Balance 1000.00 3 4 Desc Debit Credit Balance 5 2.00 998.00 6 5.00 1003.00 7 1003.00 8 1003.00 9 1003.00 10 1003.00 The Account balance is shown in cell E3, so...

Pocket Money 2003 Help
Does anyone have any clue when an update/fix will be posted for Pocket Money 2003. I bought a new Toshiba 355 with Pocket Pc 2003 and my new Money 2003 doesn't work with my new PDA (The main reason I bought both). I called support a month ago, and they said they would probably post a new download within a couple weeks. Of course, no download yet. Am I going to have to buy yet another version of Money to get it to work???? Thanks ...

Risk and Decision Analysis @Risk, Evolver, Decisiontools, Roy Kelly FloorTrader Tools 8.3, AmiBroker 5.0, MultiCharts 2.1, NinjaTrader 6, OwnData 2.6, VantagePoint 7.0.11.2, other ...
Risk and Decision Analysis @Risk, Evolver, Decisiontools, Roy Kelly FloorTrader Tools 8.3, AmiBroker 5.0, MultiCharts 2.1, NinjaTrader 6, OwnData 2.6, VantagePoint 7.0.11.2, other ... please send e-mail to : ola 'AT' mail 'DOT' gr , ola3 'AT' mailbox 'DOT' gr , ( please substitute 'AT' with '@' , and 'DOT' with '.' ) , ola@mail.gr, ola3@mailbox.gr, ======================================= RISK ANALYSIS with @RISK --- @RISK 4.5.7 for Excel, Compatible with Excel 2007, ( Standard, Porfessional, Industrial ) @RISK 4.5.6 Internat...

Winsock / Sockets programming #2
Working with Winsock I am making this post because I need help with Winsock Programming. About the application: I want to create a client/server application. At the client side receive message from the server only, send message to the server only. In the Server side receive message from all the clients, broadcast message, and send message to individual clients. So far, I have read some information about Winsock, and I have managed to create in a dialog application the win console application example on. http://www.codeproject.com/KB/cpp/chat_client_server.aspx But I am having problem wh...

HELP 2sht comparison MACRO
I have 2shts, one with old data, one with new (Changes, Adds, Deletes) The Rows could be as many as 10 columns wide and may not be in any particular order. I require some code to take the first row in sht.2 and look for it in sht.1 and tell me if it has changed (C) or is added (A) a column may be added to depict the Changes, Adds, Deletes. Could you not just use =Sheet!A1 in the first sheet and copy down? -- HTH Bob Phillips "Les Wegg" <leslie.wegg@baesystems.com> wrote in message news:42830ce0_1@glkas0286.greenlnk.net... > I have 2shts, one with old data, one with...

Help needed with MailMerge.
Using MS Word and Excel 2002 SP3. I have created a large Excel spreadsheet that comprises six sheets, each of which has many columns - one goes out to column CM, of data items. I'm now trying to create a Word MailMerge document to present the data. This is proving to be tedious, since for each item I have to 1.. click 'More items.' in the Mail Merge sidebar, which opens a dialogue box which I can 2.. scroll through to select the relevant data item, 3.. then Insert it, 4.. then close the dialogue box, 5.. go to the next field in my Word document and go through the w...

Query/Expression Help Required!
Hello, i would appreciate any help in regards to the problem i am having. I will try and explain it to the best of my ability (im a newbie to Access 2000) Here is a snipt of the information i have extracted via a query. I have been able to work out Duration via a calculation. "Agent Pin" "Start Time" "AUX Name" "End Time" "Duration" "7015" "13:45:21" "After Call Work" "13:54:18" "537" "7015" "14:30:25" "After Call Work" "14:31:03" &...

HELP! OAL Generator Error ID 9334 and 9344
We are running Exchange 2003 SP1. Users see errors in Outlook 2003 about not able to generate the Offline Address list. Checked the Event Manager and found Error ID: 9334 and Error ID: 9344 Error ID: 9334 OALGen encountered error 9004010f while initializing the offline address list generation process. No Offline address list have been generated. Error ID: 9344 OALGen could not find the address list with the Active Directory object GUID of '/guid=3B3681C2364D814F91379D5358C20302' in the list of available address lists. Please check the offline address list configurati...

additional method of online help for CRM3.0 user definition entiti
Hi all. I have a question about an additional method of online help for CRM3.0 user definition entities. A user definition entity (object_type_code:10000) and made an OnLineHelpFile (test.htm) which supported. In addition, I add following two lines to help_toc.xml. <page pro="true" sbe="true" topic="helptest" filename="/userdefined/edit.aspx?etc=10000">Customized Form Help</page> <page pro="true" sbe="true" topic="helptest" filename="/_root/homepage.aspx?etc=10000">Customized Form View Hel...

question on OnNcPaint #2
hi, I am trying to paint the border around the main frame black. This is my current code void CMainFrame::OnNcPaint() { MSG &msg = AfxGetThreadState()->m_lastSentMsg; HRGN hRgn = (HRGN)msg.wParam; CRect rcWin; // window rect GetWindowRect(&rcWin); rcWin.OffsetRect(-rcWin.left, -rcWin.top); CWindowDC dcWin(this); CBrush brFrame(RGB(0,0,0)); CRect finalRect; CRect clntRect; m_Client.GetWindowRect(clntRect); ScreenToClient(clntRect); CRect dockRect; m_Dock->GetWindowRect(dockRect); ScreenToClient(dockRect); ::Unio...

Creating a chart. #2
The code below creates a chart and places it on the Excel spreadsheet. I can;t seem to find a couple of things how can I control what is displayed on the x and y axis? and how can I control how wide or how many points are displayed. oChart = oWS.Parent.Charts.Add With oChart .ChartWizard(_ReadingArrayList3, Excel.XlChartType.xlLine, , Excel.XlRowCol.xlRows) '.ChartWizard(oResizeRange, Excel.XlChartType.xlLine, , Excel.XlRowCol.xlRows) oSeries = .SeriesCollection(1) '' oSeries.XValues = oWS.Range("A2", "A6&quo...