Chrisg33

New Member
Joined
Mar 10, 2018
Messages
4
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
You could have a separate table of products/components, then record deductions by multiplying -1 times a lookup formula and the unit total.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Picture is not shown. You need to upload print screen to an image hosting site then share link here.
 
Last edited:
Upvote 0
Still not clear from the image what you want to achieve. Here is my understanding: see before and after print screens:
Prt_Screen_Mr_01.jpg

Prt_Screen_Mr_02.jpg


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,
 
Last edited:
Upvote 0
tlone296d
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/
 
Upvote 0
Something like this could work too:


Excel 2010
ABCDEFGHI
1PartUnitsTypePartUnit
2A198Metro 1A19
3A267Metro 1A23
4A354Metro 1A33
5A4100Metro 1A44
6A5400Metro 1B15
7A623Metro 1B39
8B145Metro 1B41
9B256Metro 2A24
10B343Metro 2B23
11B423Metro 2B57
12B556Metro 2B69
13B667
14
15
16Lamp OutQuantityDate
17Metro 142/11/2018
Sheet1


Code:
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
ABCDEFGHI
1PartUnitsTypePartUnit
2A198Metro 1A19
3A267Metro 1A23
4A354Metro 1A33
5A4100Metro 1A44
6A5400Metro 1B15
7A623Metro 1B39
8B145Metro 1B41
9B256Metro 2A24
10B343Metro 2B23
11B423Metro 2B57
12B556Metro 2B69
13B667
14A1-36
15A2-12
16A3-12Lamp OutQuantityDate
17A4-16Metro 142/11/2018
18B1-20
19B3-36
20B4-4
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top