Weekly spending sums

[Guy Laurent]

I have several separate worksheets in one file containing financial
data with purchases made on certain days, eg. one of which is called
"items":

article, quantity, price, date[DMY]
screws, 1000, 3, 14/10/03
washers, 2000, 4, 25/11/03
bolts, 250, 5, 03/12/03
nuts, 500, 10, 04/12/03


On a separate sheet I would like to collate that data by weeks in a
"sum" sheet, eg.

start, end, money_spent
13/10/03, 19/10/03, 3 <-[*]
20/10/03, 26/10/03, 0
27/10/03, 02/11/03, 0
03/11/03, 09/11/03, 0
10/11/03, 16/11/03, 0
17/11/03, 23/11/03, 0
24/11/03, 30/11/03, 4
01/12/03, 07/12/03, 15


I'd like to keep these separate worksheets flexible, so that when I
add records in there, the "sum" sheet updates automatically.
Something like
IF (AND (items!D>=A2; items!D<=B2) ...)
for column C in the row marked [*] of above's "sum" sheet doesn't
obviously work.

Question is: How do I extract the data for the "money_spent" column
from those various sheets?


Any help appreciated - thanks in advance!

 

[Dave Peterson]

It looks like your week starts on a Monday.

I think I'd add a helper column (E?) and put this formula in it:

=D2-WEEKDAY(D2,3)
(and format as a date)

Use "Beginning On" as a column header.

Then select your range and do Data|Pivottable
Follow the wizard until you get to the dialog with a layout button on it. Click
that button.

Then you can drag this column header to the Row field and drag the Qty field to
the Data field.

If you really wanted both dates to show up, use this as your formula:
=TEXT(D2-WEEKDAY(D2,3),"dd/mm/yyyy")&"--"&TEXT(6+D2-WEEKDAY(D2,3),"dd/mm/yyyy")

I have several separate worksheets in one file containing financial
data with purchases made on certain days, eg. one of which is called
"items":

article, quantity, price, date[DMY]
screws, 1000, 3, 14/10/03
washers, 2000, 4, 25/11/03
bolts, 250, 5, 03/12/03
nuts, 500, 10, 04/12/03

On a separate sheet I would like to collate that data by weeks in a
"sum" sheet, eg.

start, end, money_spent
13/10/03, 19/10/03, 3 <-[*]
20/10/03, 26/10/03, 0
27/10/03, 02/11/03, 0
03/11/03, 09/11/03, 0
10/11/03, 16/11/03, 0
17/11/03, 23/11/03, 0
24/11/03, 30/11/03, 4
01/12/03, 07/12/03, 15

I'd like to keep these separate worksheets flexible, so that when I
add records in there, the "sum" sheet updates automatically.
Something like
IF (AND (items!D>=A2; items!D<=B2) ...)
for column C in the row marked [*] of above's "sum" sheet doesn't
obviously work.

Question is: How do I extract the data for the "money_spent" column
from those various sheets?

Any help appreciated - thanks in advance!