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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,151
You could have a separate table of products/components, then record deductions by multiplying -1 times a lookup formula and the unit total.
 

KeepTrying

Active Member
Joined
Aug 19, 2012
Messages
273
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

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

Active Member
Joined
Aug 19, 2012
Messages
273
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Picture is not shown. You need to upload print screen to an image hosting site then share link here.
 
Last edited:

KeepTrying

Active Member
Joined
Aug 19, 2012
Messages
273
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Chrisg33

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

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,151
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
 

Forum statistics

Threads
1,181,848
Messages
5,932,442
Members
436,838
Latest member
rparthireddy

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
Top