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?