transfer stock on condition

[bestman21]

Can someone help with a formula?
I want to use the transfer of stock between 3 shops, by checking a the cell
of Sale on each item, and then check the inventory cell if few or zero and
the same item available in other shop, will be transfer the appropriate
quantity.

Example 1:

shop1 shop2 shop3
Total QTY
sale inventory sale inventory sale inventory sale
inventory
2 9 10 1 4 0
16 10

shop1 to shop2 = 3 pcs
shop1 to shop3 = 3 pcs

to be similar to:

shop1 shop2 shop3
Total QTY
sale inventory sale inventory sale inventory sale
inventory
2 3 10 4 4 3
16 10

===================================
Example 2:


shop1 shop2 shop3
Total QTY
sale inventory sale inventory sale inventory sale
inventory
10 5 9 1 4 11
23 17

shop3 to shop2 = 5 pcs

to be similar to:

shop1 shop2 shop3
Total QTY
sale inventory sale inventory sale inventory sale
inventory
10 5 9 6 4 6
23 17

 

[bestman21]

I do not know if the examples are clear or not, anyway examples here again

Example 1:

shop1,sale = 2 inventory = 9
shop2,sale =10 inventory = 1
shop3,sale = 4 inventory = 0
Total QTY,sale = 16 inventory = 10


Transfer:
shop1 to shop2 = 3 pcs
shop1 to shop3 = 3 pcs

to be similar to:

shop1,sale = 2 inventory = 3
shop2,sale =10 inventory = 4
shop3,sale = 4 inventory = 3
Total QTY,sale = 16 inventory = 10

===================================


Example 2:

shop1,sale = 10 inventory = 5
shop2,sale = 9 inventory = 1
shop3,sale = 4 inventory = 11
Total QTY,sale = 23 inventory = 17


Transfer:
shop3 to shop2 = 5 pcs

to be similar to:

shop1,sale = 10 inventory = 5
shop2,sale = 9 inventory = 6
shop3,sale = 4 inventory = 6
Total QTY,sale = 23 inventory = 17

 

[bestman21]

Up