Lets say I run 3 stores plus a Distribution Centre (DC). Whenever a customer orders inventory at each store, I want to update that store's remaining inventory. Also, if the customer orders more inventory than what the store has in stock, then i want the formula to reduce the inventory from the DC instead.
Here is my scenarios for this example: (note: I only used 4 customers in this example, but i need the formula to have the ability to deal with several more than this).
Store1 Inventory: 200
Store2 Inventory: 200
Store3 Inventory: 200
Distribution Centre (DC) Inventory: 1000
Store1_Customer1 buys 150 items, so I need to reduce Store1 inventory down to 50. Since there is stock remaining at the store, I do not need to draw down stock at the Distribution Center (DC) at this time.
Store2_Customer2 buys 250 itmes from store2, so I need to reduce Store2 inventory down to 0 items, and reduce the DC inventory down by 50 from 1000 to 950.
Store2_Customer3 buys 100 itmes from store2, since there is 0 inventory remaining at Store2 I need to reduce the DC inventory down by 100 items from 950 to 850.
Store3_Customer4 buys 250 items from store3, so I need to reduce this store's inventory down to 0, and reduce the DC inventory by 50, from 850 to 800.
I attached a screen print of my worksheet to give you an idea of the layout.
Note: The yellow-highlighted area is where I need the formulas to update the remaining inventories.