software

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!

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

=SUMPRODUCT((Sheet2!A1:A20=quot;memphisquot;)*(Sheet2!B1:M 20))

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;chip_pypquot; gt; wrote in message
...
gt; I have a workbook with two sheets in it. the workbook is used to keep
track
gt; of machines in a particular location. on one sheet there is a weekly
update
gt; and the second sheet is a year to date tracker. what i'm trying to do is
on
gt; the weekly sheet i want to update the numbers for each location, weekly
gt; obviously. on the other sheet i want it to keep track of the yearly
amount.
gt; for example on the weekly sheet i have 3 machines in memphis and 4 in los
gt; angeles. the yearly sheet will read 3 for memphis and 4 for los angeles.
then
gt; at the end of week two i update the weekly sheet saying there were 5
machines
gt; in memphis for that week and 1 in los angeles. the yearly will then
gt; automatically update and say for the year there are 8 in memphis and 5 in
los
gt; angeles...this will keep going on thru out the whole year. surely there is
a
gt; macro for this, but what is it? thank you!
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.

quot;Bob Phillipsquot; wrote:

gt; Do you maintain the weekly figures in separate columns? If so a formula will
gt; do it.
gt;
gt; =SUMPRODUCT((Sheet2!A1:A20=quot;memphisquot;)*(Sheet2!B1:M 20))
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt; (remove nothere from the email address if mailing direct)
gt;
gt;
gt; quot;chip_pypquot; gt; wrote in message
gt; ...
gt; gt; I have a workbook with two sheets in it. the workbook is used to keep
gt; track
gt; gt; of machines in a particular location. on one sheet there is a weekly
gt; update
gt; gt; and the second sheet is a year to date tracker. what i'm trying to do is
gt; on
gt; gt; the weekly sheet i want to update the numbers for each location, weekly
gt; gt; obviously. on the other sheet i want it to keep track of the yearly
gt; amount.
gt; gt; for example on the weekly sheet i have 3 machines in memphis and 4 in los
gt; gt; angeles. the yearly sheet will read 3 for memphis and 4 for los angeles.
gt; then
gt; gt; at the end of week two i update the weekly sheet saying there were 5
gt; machines
gt; gt; in memphis for that week and 1 in los angeles. the yearly will then
gt; gt; automatically update and say for the year there are 8 in memphis and 5 in
gt; los
gt; gt; angeles...this will keep going on thru out the whole year. surely there is
gt; a
gt; gt; macro for this, but what is it? thank you!
gt;
gt;
gt;

Thought that might be the case.

Try this VBA solution

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = quot;B2:B200quot;
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(quot;Sheet3quot;).Range(quot;A:Aquot;), 0)
On Error GoTo 0
If iPos gt; 0 Then
Worksheets(quot;Sheet3quot;).Range(quot;Bquot; amp; iPos).Value = _
Worksheets(quot;Sheet3quot;).Range(quot;Bquot; amp; 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)quot;chip_pypquot; gt; wrote in message
...
gt; Unfortuantely not. on the weekly spreadsheet i have memphis in c3 then
gt; jackson in c4 and so on with about 13 other locations. there is only one
spot
gt; for the weekly figures so i wanted the yearly sheet to pull the number off
gt; the weekly and add it to a stored number that way it is always
accumulating
gt; as the weekly is updating.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Do you maintain the weekly figures in separate columns? If so a formula
will
gt; gt; do it.
gt; gt;
gt; gt; =SUMPRODUCT((Sheet2!A1:A20=quot;memphisquot;)*(Sheet2!B1:M 20))
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; RP
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt;
gt; gt; quot;chip_pypquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have a workbook with two sheets in it. the workbook is used to keep
gt; gt; track
gt; gt; gt; of machines in a particular location. on one sheet there is a weekly
gt; gt; update
gt; gt; gt; and the second sheet is a year to date tracker. what i'm trying to do
is
gt; gt; on
gt; gt; gt; the weekly sheet i want to update the numbers for each location,
weekly
gt; gt; gt; obviously. on the other sheet i want it to keep track of the yearly
gt; gt; amount.
gt; gt; gt; for example on the weekly sheet i have 3 machines in memphis and 4 in
los
gt; gt; gt; angeles. the yearly sheet will read 3 for memphis and 4 for los
angeles.
gt; gt; then
gt; gt; gt; at the end of week two i update the weekly sheet saying there were 5
gt; gt; machines
gt; gt; gt; in memphis for that week and 1 in los angeles. the yearly will then
gt; gt; gt; automatically update and say for the year there are 8 in memphis and 5
in
gt; gt; los
gt; gt; gt; angeles...this will keep going on thru out the whole year. surely
there is
gt; gt; a
gt; gt; gt; macro for this, but what is it? thank you!
gt; gt;
gt; gt;
gt; gt;