software

Hi,

The spreadsheet I am working is one that is done daily with a weekly recap
of a total of certain codes. What I want to do is have the Month to Date
total of the codes be automatic without having to link it to all the other
weekly spreadsheets.

For example: A1=1 (for week 1, 2 for week 2, etc.)

=if (A1=1,a2 ?,?) (A2 would represent the total of that code for that week)

My sheet will total the codes for that week, but how do I automate adding
Week 1's totals to Week 2's totals etc. for a Month to Date total?

Right now, I am having to manually add the current week with the past weeks
totals to get the Month to Date total.

Any ideas?

Thanks, CP

=SUM($A$1:A1)

copied down

will sum A1, then A1:A2 then A1:A3

is that what you want?

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

quot;CPquot; gt; wrote in message
...
gt; Hi,
gt;
gt; The spreadsheet I am working is one that is done daily with a weekly recap
gt; of a total of certain codes. What I want to do is have the Month to Date
gt; total of the codes be automatic without having to link it to all the other
gt; weekly spreadsheets.
gt;
gt; For example: A1=1 (for week 1, 2 for week 2, etc.)
gt;
gt; =if (A1=1,a2 ?,?) (A2 would represent the total of that code for that
gt; week)
gt;
gt; My sheet will total the codes for that week, but how do I automate adding
gt; Week 1's totals to Week 2's totals etc. for a Month to Date total?
gt;
gt; Right now, I am having to manually add the current week with the past
gt; weeks
gt; totals to get the Month to Date total.
gt;
gt; Any ideas?
gt;
gt; Thanks, CPHi

It is a little unclear from your posting as to how your data is set out.
You say you want to add data from Other sheets, to give a month to data
total. What about part weeks, to make up a month, or are you working on
13 x 4 week periods rather than months?

Could you post some more detail about how each sheet is set out?--
Regards

Roger GovierCP gt; wrote
gt; Hi,
gt; The spreadsheet I am working is one that is done daily with a weekly
gt; recap of a total of certain codes. What I want to do is have the
gt; Month to Date total of the codes be automatic without having to
gt; link it to all the other weekly spreadsheets.
gt; For example: A1=1 (for week 1, 2 for week 2, etc.)
gt; =if (A1=1,a2 ?,?) (A2 would represent the total of that code for
gt; that week) My sheet will total the codes for that week, but how do I
gt; automate adding Week 1's totals to Week 2's totals etc. for a Month
gt; to Date total? Right now, I am having to manually add the current
gt; week with the past weeks totals to get the Month to Date total.
gt; Any ideas?
gt; Thanks, CP
Hi Peo,

Thanks for your response. The workbook consists of 6 pages: one for each
day, then a recap for the week. It is done every week. The daily sheets
will calculate the days activities based on quot;codesquot; for each activity. Ex.
quot;Cquot; = quot;Cold Callquot;. So it will count up how many quot;C'squot; for the day and then
for the week on the recap. That's the easy part.

What I want it to do is 'save' the weekly count into columns for week 1,
week 2, week 3, and week 4 to get a cumulative total for the month. Say the
Weekly Recap count 10 quot;C'squot; for week 1, then 2 quot;C'squot; for week 2. When I do
the workbook for week 2, the column for the week's totals of quot;C'squot; will say 2
and the 10 from week 1 will go away. So I have to manually add the 2 to the
10 that I've manually entered into the MTD column next to the Week column. I
want to be able to 'save' the 10, then the 2 and so on so that process can be
automatic.

I hope that clarifies my question better.

Thanks, CP

quot;Peo Sjoblomquot; wrote:

gt; =SUM($A$1:A1)
gt;
gt; copied down
gt;
gt; will sum A1, then A1:A2 then A1:A3
gt;
gt; is that what you want?
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Northwest Excel Solutions
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;CPquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; The spreadsheet I am working is one that is done daily with a weekly recap
gt; gt; of a total of certain codes. What I want to do is have the Month to Date
gt; gt; total of the codes be automatic without having to link it to all the other
gt; gt; weekly spreadsheets.
gt; gt;
gt; gt; For example: A1=1 (for week 1, 2 for week 2, etc.)
gt; gt;
gt; gt; =if (A1=1,a2 ?,?) (A2 would represent the total of that code for that
gt; gt; week)
gt; gt;
gt; gt; My sheet will total the codes for that week, but how do I automate adding
gt; gt; Week 1's totals to Week 2's totals etc. for a Month to Date total?
gt; gt;
gt; gt; Right now, I am having to manually add the current week with the past
gt; gt; weeks
gt; gt; totals to get the Month to Date total.
gt; gt;
gt; gt; Any ideas?
gt; gt;
gt; gt; Thanks, CP
gt;
gt;

Hi Roger,

Thanks for your reply. Please see my reply to Peo for more clarification.
What I want to do is to get cumulative totals without having to link to other
workbooks for them.

Thanks, CP

quot;Roger Govierquot; wrote:

gt; Hi
gt;
gt; It is a little unclear from your posting as to how your data is set out.
gt; You say you want to add data from Other sheets, to give a month to data
gt; total. What about part weeks, to make up a month, or are you working on
gt; 13 x 4 week periods rather than months?
gt;
gt; Could you post some more detail about how each sheet is set out?
gt;
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt;
gt;
gt;
gt; CP gt; wrote
gt; gt; Hi,
gt; gt; The spreadsheet I am working is one that is done daily with a weekly
gt; gt; recap of a total of certain codes. What I want to do is have the
gt; gt; Month to Date total of the codes be automatic without having to
gt; gt; link it to all the other weekly spreadsheets.
gt; gt; For example: A1=1 (for week 1, 2 for week 2, etc.)
gt; gt; =if (A1=1,a2 ?,?) (A2 would represent the total of that code for
gt; gt; that week) My sheet will total the codes for that week, but how do I
gt; gt; automate adding Week 1's totals to Week 2's totals etc. for a Month
gt; gt; to Date total? Right now, I am having to manually add the current
gt; gt; week with the past weeks totals to get the Month to Date total.
gt; gt; Any ideas?
gt; gt; Thanks, CP
gt;
gt;
gt;

Hi CP

I would be inclined to keep all the data on a single sheet.
Supposing you have a header in row 1, with Date in A1 and Calls in B1
A2 onward conatins a list of dates, B2 onwards has quot;Cquot; in some of the
cells.

In D1 Enter Week1 and copy across through E1:H1 so you have Week1
through Week5, enter Month in I1
In cell D2 enter the start of the first week you are interested in.
In E2 enter =D2 7 and copy across through F2:H2. In I2 enter =D2
In D3 enter
=SUMPRODUCT(--($A$2:$A$1000gt;=D2),--($A$2:$A$1000lt;E2),--($B$2:$B$1000=quot;Cquot;))
Copy across through cells E3:H3
In cell I3 enter
=SUMPRODUCT(--(MONTH($A$2:$A$1000)=MONTH(I2)),--($B$2:$B$1000=quot;Cquot;))

Now, all you have to do is alter the date in D2, and you will have the
numbers for each week in the month, and the cumulative for the month.

Play around with this idea to find what suits you. Change the ranges to
suit, but do not use whole columns as a range, Sumproduct doesn't handle
whole columns like A:A.--
Regards

Roger GovierCP gt; wrote
gt; Hi Roger,
gt; Thanks for your reply. Please see my reply to Peo for more
gt; clarification. What I want to do is to get cumulative totals
gt; without having to link to other workbooks for them.
gt; Thanks, CP
gt; quot;Roger Govierquot; wrote:
gt;gt; Hi
gt;gt; It is a little unclear from your posting as to how your data is
gt;gt; set out. You say you want to add data from Other sheets, to give
gt;gt; a month to data total. What about part weeks, to make up a month,
gt;gt; or are you working on 13 x 4 week periods rather than months?
gt;gt; Could you post some more detail about how each sheet is set out?
gt;gt; --
gt;gt; Regards
gt;gt; Roger Govier
gt;gt; CP gt; wrote
gt;gt;gt; Hi,
gt;gt;gt; The spreadsheet I am working is one that is done daily with a weekly
gt;gt;gt; recap of a total of certain codes. What I want to do is have the
gt;gt;gt; Month to Date total of the codes be automatic without having to
gt;gt;gt; link it to all the other weekly spreadsheets.
gt;gt;gt; For example: A1=1 (for week 1, 2 for week 2, etc.)
gt;gt;gt; =if (A1=1,a2 ?,?) (A2 would represent the total of that code for
gt;gt;gt; that week) My sheet will total the codes for that week, but how do I
gt;gt;gt; automate adding Week 1's totals to Week 2's totals etc. for a Month
gt;gt;gt; to Date total? Right now, I am having to manually add the current
gt;gt;gt; week with the past weeks totals to get the Month to Date total.
gt;gt;gt; Any ideas?
gt;gt;gt; Thanks, CP