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]

ADVERTISEMENT

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]

ADVERTISEMENT

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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">Part</td><td style=";">Units</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Type</td><td style=";">Part</td><td style=";">Unit</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;">A1</td><td style="text-align: right;;">98</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">A1</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="color: #333333;;">A2</td><td style="text-align: right;;">67</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">A2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #333333;;">A3</td><td style="text-align: right;;">54</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">A3</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #333333;;">A4</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">A4</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="color: #333333;;">A5</td><td style="text-align: right;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">B1</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #333333;;">A6</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">B3</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="color: #333333;;">B1</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">B4</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="color: #333333;;">B2</td><td style="text-align: right;;">56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 2</td><td style=";">A2</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="color: #333333;;">B3</td><td style="text-align: right;;">43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 2</td><td style=";">B2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="color: #333333;;">B4</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 2</td><td style=";">B5</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="color: #333333;;">B5</td><td style="text-align: right;;">56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 2</td><td style=";">B6</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="color: #333333;;">B6</td><td style="text-align: right;;">67</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Lamp Out</td><td style=";">Quantity</td><td style=";">Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2/11/2018</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">Part</td><td style=";">Units</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Type</td><td style=";">Part</td><td style=";">Unit</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;">A1</td><td style="text-align: right;;">98</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">A1</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="color: #333333;;">A2</td><td style="text-align: right;;">67</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">A2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #333333;;">A3</td><td style="text-align: right;;">54</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">A3</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #333333;;">A4</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">A4</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="color: #333333;;">A5</td><td style="text-align: right;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">B1</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #333333;;">A6</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">B3</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="color: #333333;;">B1</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style=";">B4</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="color: #333333;;">B2</td><td style="text-align: right;;">56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 2</td><td style=";">A2</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="color: #333333;;">B3</td><td style="text-align: right;;">43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 2</td><td style=";">B2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="color: #333333;;">B4</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 2</td><td style=";">B5</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="color: #333333;;">B5</td><td style="text-align: right;;">56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 2</td><td style=";">B6</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="color: #333333;;">B6</td><td style="text-align: right;;">67</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">A1</td><td style="text-align: right;;">-36</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">A2</td><td style="text-align: right;;">-12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">A3</td><td style="text-align: right;;">-12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Lamp Out</td><td style=";">Quantity</td><td style=";">Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">A4</td><td style="text-align: right;;">-16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Metro 1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2/11/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">B1</td><td style="text-align: right;;">-20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">B3</td><td style="text-align: right;;">-36</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">B4</td><td style="text-align: right;;">-4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />