Inventory management for excel

[ellyzadg]

Hi all,

I'm making an inventory management system for warehouse tools.
Basically it would contain a Main Database and Database for Plant A and Plant B (Moving inventory, which records the in and out of tools)

Im looking for a formulas that can be incorporated in the Main Database so the Moving of inventory between two plant will reflect to it. I only include database for plant A since plant B will be the same on it. Hope you can help me. Thank you!

Sheet 1 ( Main Database)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Material Code[/TD]
[TD]Category[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Description[/TD]
[TD]Quantity Plant A[/TD]
[TD]Quantity
Plant B[/TD]
[TD]Repair Plant A[/TD]
[TD]Repair Plant B[/TD]
[TD]Scrap Plant A[/TD]
[TD]Scrap Plant B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0001
[/TD]
[TD]Accessories[/TD]
[TD]A[/TD]
[TD]Product 1[/TD]
[TD]sample1[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0002[/TD]
[TD]Accessories[/TD]
[TD]B[/TD]
[TD]Product 2[/TD]
[TD]sample2[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0003[/TD]
[TD]Tools[/TD]
[TD]C[/TD]
[TD]Product 3[/TD]
[TD]sample3[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0004[/TD]
[TD]Tools[/TD]
[TD]D[/TD]
[TD]Product 4[/TD]
[TD]sample4[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 ( Moving Inventory -Database for Plant A)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Transaction Type[/TD]
[TD]Material Code[/TD]
[TD]Category[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Description[/TD]
[TD]Available Quantity[/TD]
[TD]Quantity (to withdraw or receive)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]05/22/2016[/TD]
[TD]Issue[/TD]
[TD]0001[/TD]
[TD]Accessories[/TD]
[TD]A[/TD]
[TD]Product 1[/TD]
[TD]sample1[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]05/22/2016[/TD]
[TD]Receive as New[/TD]
[TD]0002[/TD]
[TD]Accessories[/TD]
[TD]B[/TD]
[TD]Product 2[/TD]
[TD]sample2[/TD]
[TD]15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]05/22/2016[/TD]
[TD]Receive from Repair[/TD]
[TD]0003[/TD]
[TD]Accessories[/TD]
[TD]C[/TD]
[TD]Product 3[/TD]
[TD]sample3[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]05/23/2016[/TD]
[TD]Transfer to Plant B[/TD]
[TD]0001[/TD]
[TD]Accessories[/TD]
[TD]A[/TD]
[TD]Product 1[/TD]
[TD]sample1[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]05/23/2016[/TD]
[TD]Transfer for Repair[/TD]
[TD]0001[/TD]
[TD]Accessories[/TD]
[TD]A[/TD]
[TD]Product 1[/TD]
[TD]sample1[/TD]
[TD]10[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]05/23/2016[/TD]
[TD]Transfer for Disposal[/TD]
[TD]0001[/TD]
[TD]Accessories[/TD]
[TD]A[/TD]
[TD]Product 1[/TD]
[TD]sample1[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 1 ( Main Database) (which is the desired output)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Material Code[/TD]
[TD]Category[/TD]
[TD]Type[/TD]
[TD]Name[/TD]
[TD]Description[/TD]
[TD]Quantity Plant A[/TD]
[TD]Quantity
Plant B[/TD]
[TD]Repair Plant A[/TD]
[TD]Repair Plant B[/TD]
[TD]Scrap Plant A[/TD]
[TD]Scrap Plant B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0001
[/TD]
[TD]Accessories[/TD]
[TD]A[/TD]
[TD]Product 1[/TD]
[TD]sample1[/TD]
[TD]2 [/TD]
[TD]21 [/TD]
[TD]6 [/TD]
[TD]10[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0002[/TD]
[TD]Accessories[/TD]
[TD]B[/TD]
[TD]Product 2[/TD]
[TD]sample2[/TD]
[TD]16 [/TD]
[TD]25[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0003[/TD]
[TD]Tools[/TD]
[TD]C[/TD]
[TD]Product 3[/TD]
[TD]sample3[/TD]
[TD]12 [/TD]
[TD]12[/TD]
[TD]1 [/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0004[/TD]
[TD]Tools[/TD]
[TD]D[/TD]
[TD]Product 4[/TD]
[TD]sample4[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]2

[/TD]
[/TR]
</tbody>[/TABLE]

If transaction type is:
1. Issue: There should be deducted quantity from the Main Database on their respective Plant
2. Receive as New: This type is when the Plant has received new tools; also added on the quantity of each respective plant
3. Receive from Repair: Since there is assumption that tools can be subjected for repair, quantity under Repair Column on each respective plant will be deducted then Available Quantity that can be withdraw would have an additional
4. Transfer to Another plant: E.g Plant A can transfer stock to plant B, so stock on plant A would have a deduction while Plant B would have and additional
5. Transfer for Repair: If plant would subject tools for repair, then, available quantity would have a deduction while the repair quantity would have an additional
6. Transfer for Disposal: Available quantity will have a deduction while the scrap quantity would have an additional.

* All data should work based on their respective Plant and Material Code