Excel Manufacturing inventory

[Chrisg33]

Recently got promoted at my job to start and manage inventory. We have a set up excel sheet but currently have to manually update inventory when a product leaves. To keep it short for example product one uses 5 screws, 2 nuts, and one hinge. How would I make it so that if I enter that we sold 5 units of product one that it would remove the respective quantities of screws, nuts, and hinges in the main inventory page? Thank you for your time.

 

[sheetspread]

You could have a separate table of products/components, then record deductions by multiplying -1 times a lookup formula and the unit total.

 

[sheetspread]

I tried replying to your private message but the box is full. Here's how to use vlookup:

https://www.techonthenet.com/excel/formulas/vlookup.php

(a cell reference can be used instead of the hard coded 10251)

 

[KeepTrying]

Hi Chrisg33,
I think a print screen would be useful about your sample data layout. I have an idea but I don't know how your data look like.

 

[Chrisg33]

Hi Keep Trying,

don't know if that worked. Basically when I put a value into the lamps out section I want it to remove the according values from the table I gave in the top right to the main inventory on the top left.

 

[KeepTrying]

Picture is not shown. You need to upload print screen to an image hosting site then share link here.

 

[Chrisg33]

https://postimg.org/image/n17miw4ut/

 

[KeepTrying]

Still not clear from the image what you want to achieve. Here is my understanding: see before and after print screens:

Range H2 and H3 shows inventory for Product01 and Product02.
Range J1:K3 and range M1:N3 are help tables (how many screw, nut and hinge is needed for a product)
Range C2:C4 shows inventory for screw, nut etc.

So e.g. Product01 needs 5 screws, 2 nuts and 1 hinge. If I reduce value for Product01 (in range H2 from 50 to 49) then range C2:C4 needs to change as following:
- C2 (screw): from 100 to 95
- C3 (nut): from 100 to 98
- C4 (hinge): from 100 to 99

I wrote a macro which does that. If you're interested I can post code here or please refine your request.

Regards,

 

[Chrisg33]

keeptrying,
thank you that is veru similar to what I want to do. The section where I marked as #1 I want it to be like an invoice. so the number doesn't count down, but rather if I did 5 lamps I would just insert the #5 and it would update the inventory accordingly. Thank you https://postimg.org/image/tlone296d/

 

[sheetspread]

Something like this could work too:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Part	Units					Type	Part	Unit
2	A1	98					Metro 1	A1	9
3	A2	67					Metro 1	A2	3
4	A3	54					Metro 1	A3	3
5	A4	100					Metro 1	A4	4
6	A5	400					Metro 1	B1	5
7	A6	23					Metro 1	B3	9
8	B1	45					Metro 1	B4	1
9	B2	56					Metro 2	A2	4
10	B3	43					Metro 2	B2	3
11	B4	23					Metro 2	B5	7
12	B5	56					Metro 2	B6	9
13	B6	67
14
15
16							Lamp Out	Quantity	Date
17							Metro 1	4	2/11/2018
Sheet1

Sub updatelist()
Dim i%, x%, lr1%, lr2%
lr1 = Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Cells(Rows.Count, 7).End(xlUp).Row
x = 1
For i = 2 To lr2 - 5
If Cells(i, 7) = Cells(lr2, 7) Then
Cells(lr1 + x, 1) = Cells(i, 8)
Cells(lr1 + x, 2) = Cells(i, 9) * Cells(lr2, 8) * -1
x = x + 1
Else
End If
Next
End Sub

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Part	Units					Type	Part	Unit
2	A1	98					Metro 1	A1	9
3	A2	67					Metro 1	A2	3
4	A3	54					Metro 1	A3	3
5	A4	100					Metro 1	A4	4
6	A5	400					Metro 1	B1	5
7	A6	23					Metro 1	B3	9
8	B1	45					Metro 1	B4	1
9	B2	56					Metro 2	A2	4
10	B3	43					Metro 2	B2	3
11	B4	23					Metro 2	B5	7
12	B5	56					Metro 2	B6	9
13	B6	67
14	A1	-36
15	A2	-12
16	A3	-12					Lamp Out	Quantity	Date
17	A4	-16					Metro 1	4	2/11/2018
18	B1	-20
19	B3	-36
20	B4	-4
Sheet1