Excel 2007 : Weekly sum of deliverables resulting in a do not exceed value

[Mickyank]

Hello All,

I'm new to this thread so excuse me if I'm not posting to the correct board. I'm trying to generate a formula which advises the user that their weekly count is at 25. We generate orders and deliver quantity of goods. Our capacity is maxed out at 25, so we don't want the order takers to take more than 25 goods per week. If the quantity ordered goes above 25 (taking previous orders into account as well) we want to simply highlight this so the order taker moves the quantity to another week. For example, we have bookings of the following:

20 pieces due 3/28 and 5 due 3/29. If we receive another order for 5 pieces on 3/29, I want to highlight or generate a message once the user tries to enter it. (The data being entered is in another tab. I want the table to pull the summary from that list.

The list in another spreadsheet looks like this:
Qty Date
Product X 20 3/28/2012
Product Y 5 3/29/2012
Product Z 5 3/29/2012

The table in another tab, will automatically post the following:
Product X Product Z Product Y TTL
3/26/2012 4/1/2012 20 5 5 30
3/9/2012 4/15/2012 0
4/16/2012 4/22/2012 0
4/23/2012 4/29/2012 0


Thanks for your help!!

[dilipandey]

Hi Mickyank,

Welcome to the forum.

I think rolling sum against date can help here.. suggest you to post a sample file. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>