Summary - For WillR or anyone else willing

• WillR,

Well, here's the spreadsheet with the basic info in it.

I hope this helps clarify my problem.

For anyone else viewing this here's the whole deal.

I have two basic data worksheets

1)Part Info
2)Daily Parts Usage

Part Info has these 3 columns
- Part Name, Subpart Name, Subpart Weight

Say for this example
- I have three parts (Part A, Part B & Part C)
- Each part is made up of three subparts as such
Part A: SubA1, SubA2, SubA3
Part B: SubA1, SubB2, SubA3
Part C: SubA1, SubA2, SubC3

Daily Parts Usage has these columns
- Date, Part A, Part B and Part C

The Part A, Part B and Part C columns are where I enter usages for those days.
The Date column will be all dates from 01/01/2003 to 12/31/2008.

What I need is to have a summary for MONTHLY usage for each part (A, B & C)

AND

a summary that shows the monthly weight used for each Subpart (i.e. 200 used in Jan, Weight of Subpart A1 in A is 2 lb therefore weight for January is 200*2 = 400 lbs.).

This needs to take into account the other Parts (SubpartA1 will have a different weight in different parts). I also need to show the moving 12-month total for each subpart (for Dec 2003 – I’ll add Jan 2003 till December 2003, for Jan 2004 – I’ll add Feb 2003 to Jan 2004 and so on).

I can use a PivotTable to show a monthly summary of usage or I could even use formulas. But I am stuck as to how to create a summary for the monthly subpart weights and moving totals?!

Any ideas?

• Please give me your attachment again as xls format.

• I've done this in Access but need to try to do this in Excel.