Dynamic 5-week date range & SUMPRODUCT formulas


Mar 1, 2011
Reaction score
Hi All,

Request your help in automating my workbook.(pls see attachment):)

All employees enter their daily data in RawData Wrksht. For certain reasons, NO SORTING ALLOWED!

For reporting purposes, currently, CLR=CLR+led+3dld+Dlm....So i have 2 wkly rpts - CLR & CENT.

The WklyRpt Wrksht should automatically pickup the values and populate each employee's cumulative data (in the respective weekly range) as & when data is entered (or on saving /exiting).

Every Monday, the last 5th week should be automatically dropped and the current week range to be added in the highlighted yellow cell (yes, split month days are okay, e.g. 31Jan - 4Feb / 28Mar - 01Apr etc) and all other week range to move left

The MnthlyRpt WrkSht to be auto populated with respective employees data when data is entered in RawData Wrksht (or on saving / exiting). Also, different month days to be taken care of - e.g. 28 / 30 / 31 days

Currently, I have a Pivot table as above and i extract the data from it and manually paste it in the Wkly & Mnthly Wrkshts since i'm not aware how to link each employees data to the respective dynamic weekly headers using SUMPRODUCT nor how to pull the data into the mnthly report using GETPIVOTDATA.

Please let me know if you need any further info..​


  • TestData.zip
    5.9 KB · Views: 82


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question