Posts by Cosmos75

  • 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?