Simple macro help

[Guest]

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!

 

[Bob Phillips]

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)

 

[Guest]

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]

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)