Generating a dynamic monthly summary sheet based on weekly data sh

[Demand Planner]

Hi, my first time using a discussion forum so go easy on me...

SITUATION:
I have a sheet ("weekly demand sheet") 8000 rows, 100 columns. Rows contain
various reference information i.e. unique product reference, product family,
etc plus demand data by week for 80 weeks.

The "weekly demand sheet" will continue to have new rows added to it as new
products are created and new columns as the weeks go by. The demand data for
a given week should not change once inputted.

PROBLEM:
I would like to create a sheet ("monthly demand sheet") in a seperate
workbook where each unique product reference is listed and a column summing
the weekly demand data by month displayed.

I would like the "monthly demand sheet" to be updated automatically when new
products are added to the "weekly demand sheet".

It seems straight forward but I'm not having much joy, so would greatly
appreciate any advice.

 

[JLatham]

What I came up with is a combination if IF() used with SUMPRODUCT() on the
Monthly sheet to 'roll up' values from the Weekly sheet. Combined with some
worksheet _Change() event handling code to deal with new entris on the Weekly
sheet, this may help you out.

I've uploaded a demo file (Excel 97-2003 compatible with macros in it) that
should show you how to get all of this done. If you need more assistance,
you can email me at (remove spaces) HelpFrom @ JLathamsite.com

You'll need to make changes to some Const values declared in the code to
make it work with your actual workbook setup - I've identified which ones
those are in the code.

 

[JLatham]

Sorry - forgot to include the link to the file, and lost connection due to
weather and had to wait until I got into the office this morning to 'fix' it.

File is at
http://www.jlathamsite.com/uploads/forDemandPlanner.xls