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)

	A	B	C	D	E	F	G	H	I	J
1	Material Code	Category	Type	Name	Description	Quantity Plant A	Quantity Plant B	Repair Plant A	Repair Plant B	Scrap Plant A	Scrap Plant B
2	0001	Accessories	A	Product 1	sample1	10	20	5	10	2	1
3	0002	Accessories	B	Product 2	sample2	15	25	4	5	7	2
4	0003	Tools	C	Product 3	sample3	10	12	3	2	4	2
5	0004	Tools	D	Product 4	sample4	10	11	2	4	4	2

<tbody>
</tbody>

Sheet 2 ( Moving Inventory -Database for Plant A)

	A	B	C	D	E	F	G	H	I
1	Date	Transaction Type	Material Code	Category	Type	Name	Description	Available Quantity	Quantity (to withdraw or receive)
2	05/22/2016	Issue	0001	Accessories	A	Product 1	sample1	10	2
3	05/22/2016	Receive as New	0002	Accessories	B	Product 2	sample2	15	1
4	05/22/2016	Receive from Repair	0003	Accessories	C	Product 3	sample3	10	2
5	05/23/2016	Transfer to Plant B	0001	Accessories	A	Product 1	sample1	10	1
6	05/23/2016	Transfer for Repair	0001	Accessories	A	Product 1	sample1	10	3
7	05/23/2016	Transfer for Disposal	0001	Accessories	A	Product 1	sample1	10	2

<tbody>
</tbody>

Sheet 1 ( Main Database) (which is the desired output)

	A	B	C	D	E	F	G	H	I	J
1	Material Code	Category	Type	Name	Description	Quantity Plant A	Quantity Plant B	Repair Plant A	Repair Plant B	Scrap Plant A	Scrap Plant B
2	0001	Accessories	A	Product 1	sample1	2	21	6	10	3	1
3	0002	Accessories	B	Product 2	sample2	16	25	4	5	7	2
4	0003	Tools	C	Product 3	sample3	12	12	1	2	4	2
5	0004	Tools	D	Product 4	sample4	10	11	2	4	4	2

<tbody>
</tbody>

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