I could use some help solving this problem. I have two sheets, one with orders the second with sales. I need help automatically subtracting from orders based on sales.
Order sheet: Column A is location, D is SKU, and F is Quantity.
Sold Sheet: Coulmn E is location, H is SKU, and I is Quantity.
Is it possible to have a script to run through every sale and if location and item are exactly the same, subtract quantity sold from the quantity ordered to give a new "due" quantity?
Worth noting, the order sheet is 18 months out for weekly dispatch. I need a way to limit the subtraction to only what is due before a certain date (column G in order sheet) There could be multiple sales to cover order in a given week.
I am essentially trying to find out what I still owe or am late on shipping. Sale is > Order, a negative number would work just fine.