I have a monthly forecast by SKU that needs to be put into a rolling 52 week schedule (M-F) starting with 1/5/09. I have my forecast on one tab and my weekly historic forecast on another, with the SKU I need in cell A1. The sheet I need the data pulled over to is set up like this:
A B C D E
Item A 1/5-1/9 1/12-1/16 1/19-1/23
My forecast sheet is set up like this:
A B C D E F
Item Jan Feb Mar Apr May
ItemA 200 90 60 145 410
I need to figure out a way to divide the total monthly forecast into the individual work weeks and lookup based off of the SKU in A1. Also, there are some weeks that have 2 months (ex: 3/30/09-4/3/09) that somehow needs to have the # of days from March mulitplied by the March forecast and the # of days in April multiplied by the April forecast.
If anyone has any suggestions of formulas or a different layout for this spreadsheet, I would really apreciate it.