Questions regarding Cell Referencing

First let me explain what I want to do. I know that these things can be done, but I want to automate the process better.

I have a spreadsheet that gives a general listing of orders.
1rst Column - lists the day of the week (Sun-Mon-Tue,etc).
2nd Column - total orders made
3rd Column - Week Number (only if 1rst Col=Sunday)
4th Column - Weekly Total (only if 1rst Col=Sunday)
5th Column - Weekly Average
6th Column - Monthly Total
7th Column - Monthly Average
8th Column - Yearly Total
9th Column - Yearly Average

What I want to do is calculate the Montly Total without using a =SUM() and highlighting the cells. Is there any module that can determine the last day of the month and automatically generate the Sum based on the first day and the last day of the month.

Next thing I want to do is create a chart based on Week Number, Week Total Week Average.

Any help would be greatly appreciated!

Hparteep

Based on my understanding of what you want as your starting point, try this:

1. Put your headings in row 1 and start data in row 2
2. Columns A and B are for date and count (i.e. no change)
3. Column C is for Week_No. Before entering this formula, install the analysis toolpak (select tools add-ins and click the box for Analysis Toolpak; may need to install from CD).
4. In C2, enter
=WEEKNUM(A2,1)
Note: this is for weeks starting on Sundays. You can change the parameter for any day you choose See help after installing the Analysis Toopak.
5. Column D is for Month_No
6. In D2, enter
=MONTH(A2)
7. Copy the formulae in C2 and D2 down as far as needed
8. Create a table for week production
in G1, H1 and I1 enter "Weekno"; "Weektotal"; and "WeekMovAvg" respectively as your headings
9. Populate G2 and below with the week numbers
10. In H2 enter
=SUMPRODUCT($B$2:$B$55,--($C$2:$C$55=$G2))
11. In I2 enter
=INT(AVERAGE($H$2:$H2))
Copy H2 and I2 down as required
12. Create a table for monthly production
in K1, L1 and M1 enter "Monthno"; "Monthtotal"; and "MonthMovAvg"
respectively as your headings
13. Populate K2 and below with month numbers
14. In L2 enter
=SUMPRODUCT($B$2:$B$55,--($D$2:$D$55=$K2))
15. In M2 enter
=INT(AVERAGE($L$2:$L2))
Copy L2 and M2 down as required
16. The formula for the year 2006 total is =SUMPRODUCT($B$2:$B$55,--(YEAR($A$2:$A$55)=2006))
17. Move the tables around and finish off the spreadsheet as you see fit.
18. You'll get basic charts by putting the cursor in the table areas and pressing the F10 key.
19. If you want to have multiple years data then you'll need to make some minor changes, but worry about that later.
20. I'd normally have your based data on one sheet, and your reports on one or more different sheets.

Good luck,