Excel add up to equal or greater than another cell using oldest date first

[RDM1126]

Hello, new here and I hope someone can help. Not sure if what I need excel to do is just to complex or it is just me.
I have a huge excel sheet with 20 plus pages that I am creating for my work,
where I'm stuck is with items that have varied weights or amounts.
I have a sheet that shows what the customer ordered then excel sorts through our entire warehouse, for example the customer ordered 16000 pounds of potatoes, and they come in 1500lbs totes my sheet knows I need to ship 11 totes to meet their requested amount.
now I get to labels which the normal case amount is 4000 so my sheet will calculate if the customer orders 17,623 labels my sheet will want to send 20,000 labels, but we may have partial rolls that together would add up to 17,956, which is what I'm trying to find a formula to count up to the smallest number that is equal or greater than what is ordered, this would also be lot code (date code) specific, in other words we have to send oldest dates first so I'm looking for help with a formula to do this, to look at dates which is a LD followed by a 6 digit date, then only count up to the smallest amount to equal or be greater than another cell (the cell with the amount requested).

Any help is appreciated and even if I need to use a few helper cells, I have a whole sheet of helpers.

 

[B4andafter]

Hello, to start, are you able to sort the whole dta by date series and have the oldest on top?..If so it would make match easier to proceed.
I would also identify all of the items that are partial by adding a helper column to the data.

Also, I know you asked for a formula but are you able to add a macro to this file? Let me know and let's see what I can do.

 

[RDM1126]

Yes to both ?'s, but I need something automated as I only work 1 shift, but 3 shifts would use this. Data Entry maybe done by 3 or 4 differet people, and maybe 6 or 7 using throughout the day and either we will set up through office online that we all have and share one constant file or save and send over and over, but most sheets of data and helpers I would only have access, others have certain areas or sheets they would work on such as taking the customer pre orders and entering the data to form an order form and cover sheet for that order, so when that order is then requested you type in the BT number which will bring up order forms and cover sheets for all orders of that BT that we have gotten but have yet to be requested, that are identical to originals but we used to or will use to have to print both sheets and keep 70 plus folders of pre orders that we have to thumb through several times a day, but those other then me will not have access to the eternal workings, they just have to export from our operating system from a certain screen our current inventory and refresh anytime an inbound comes in, but we all know how to do this already, getting other non excel people involved with adding macro or having to get into the workings is not good idea, I would take 20 helper cells or rows, everything else works flawlessly except when we have partial rolls of labels that must go by date and add up to or greater than, than another cell aka requested amount, I have other factors such as helpers to identify if any of the locations of requested items are already in a picked location or damaged location or hold location so it will not put these into the pool, or those that don't match a certain status like shippable, damaged, reserved etc, I appreciate any and all help and I'll try about anything. I have over a year working on this, and am on version 2.7 and want to release at version 3. Have couple more fixes to do but this is my big hurdle, sorry for long reply and thanks for the response.

 

[RDM1126]

Yes to both ?'s, but I need something automated as I only work 1 shift, but 3 shifts would use this. Data Entry maybe done by 3 or 4 differet people, and maybe 6 or 7 using throughout the day and either we will set up through office online that we all have and share one constant file or save and send over and over, but most sheets of data and helpers I would only have access, others have certain areas or sheets they would work on such as taking the customer pre orders and entering the data to form an order form and cover sheet for that order, so when that order is then requested you type in the BT number which will bring up order forms and cover sheets for all orders of that BT that we have gotten but have yet to be requested, that are identical to originals but we used to or will use to have to print both sheets and keep 70 plus folders of pre orders that we have to thumb through several times a day, but those other then me will not have access to the eternal workings, they just have to export from our operating system from a certain screen our current inventory and refresh anytime an inbound comes in, but we all know how to do this already, getting other non excel people involved with adding macro or having to get into the workings is not good idea, I would take 20 helper cells or rows, everything else works flawlessly except when we have partial rolls of labels that must go by date and add up to or greater than, than another cell aka requested amount, I have other factors such as helpers to identify if any of the locations of requested items are already in a picked location or damaged location or hold location so it will not put these into the pool, or those that don't match a certain status like shippable, damaged, reserved etc, I appreciate any and all help and I'll try about anything. I have over a year working on this, and am on version 2.7 and want to release at version 3. Have couple more fixes to do but this is my big hurdle, sorry for long reply and thanks for the response.

Oh and not only date (oldest first) but any partial should go first, so if you send lot (LD070722 and LD070822) and customer ordered 17560 labels and we had 15000 of lot LD070722 (the oldest) and 2559 in total of partial rolls of the newer date LD070822 we would need to send 1 full case of 4000 because the partials were 1 label short, but the goal is to reduce partial rolls, the plant will use the partials first and send us back 3,999 aka 1 full roll of 2000 and a partial roll of 1999, they are now both partials cause they are less then 1 full case of 4000.

 

[B4andafter]

One more question. Can we also move all partials to a dedicated separate sheet and keep them there? This way you can keep adding to this partial list and as they are being used up we can have a column with availability, if zero then we move on down to next partial batch for shient.
Also, the best option here is to creat a userform with a combox for all users to enter data.