Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a dynamic monthly summary sheet based on weekly data sh
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a dynamic monthly summary sheet based on weekly data sh
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. "Demand Planner" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a dynamic monthly summary sheet based on weekly dat
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 "JLatham" wrote: 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. "Demand Planner" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating dynamic charts based on the user input | Charts and Charting in Excel | |||
Collecting weekly and monthly totals from daily data | Excel Worksheet Functions | |||
Function for generating monthly & weekly averages | Excel Worksheet Functions | |||
Converting Weekly Data into Monthly Averages | Excel Worksheet Functions | |||
Converting Weekly Data into Monthly Averages | Excel Discussion (Misc queries) |