Hi. I'm a newbie here. I've been searching online for awhile on assistance on coming up with an inventory allocation formula with no luck. I turned to this website as most of the search hits I've gotten are from here.

I've been assigned a project where I'm suppose to report inventory allocation to projects at end of day every Thursday. The past 2 Thursdays, I've just literally been manually plugging in the numbers into the allocated stock column. I figured there's should be a better/faster way of doing this via a formula.

The spreadsheet I came up with is attached. The 1st tab is the demand showing the needed quantities per item and respective due/on-site dates. The on-hand inventory (stock) is on the 2nd tab. My current process is:
1. once items have been shipped, I delete those entries/rows from the first tab. This way, only all the outstanding/open orders remain.
2. after completing #1, I then copy and paste on-hand inventory from our database (Navision) onto the 2nd tab.
3. I manually plug in the the qty for each item depending on stock available. This is the part that I would like to streamline.

Hope someone here can help. Thanks.Inventory Allocation.xlsx