Subtract list of items from Inventory

Benny Neufeld

Hi,

I have a Inventory Sheet with list of items and also the quantity of each item. I also have products that use various items from the Inventory list, for example: Product #1 = 2 screws, 1 sign, and 2 hooks. And the way I have set it up now, every time when I sell Product #1 I have to manually search up each item and subtract it. Is there any way that when I insert "Product #1 sold" in a row, that it would automatically search the items in the inventory and subtract them?

Thanks in advance!

Paul Newcome

Ok. I want to make sure I understand what you are trying to do. Please feel free to correct as needed...

You go to the "Orders" sheet and enter SKU 876273 with a quantity of 6.

You want to then subtract the following from the "Inventory" sheet:

Wheels - 24

Axle - 12

Nuts - 30

Plywood - 30

Lights - 24

5' Wires - 12

8' Metal Tubing - 48

Screws - 90

Which leaves your "Inventory" sheet with (in the same order as above...

26

24

170

50

54

2

52

-5

Or were you wanting to only subtract 6 of each part from the totals on the "Inventory" sheet?

Paul Newcome

Yes. Can you show some screenshots with sensitive/confidential information removed, blocked, and/or replaced with "dummy data" as needed?

Ben Goldblatt

Hi Benny,

The Inventory Management and Inventory Stock Control templates from the Solution Center may be a good place to start out for this type of tracking. Feel free to send us some screenshots (as Paul requested with no sensitive or confidential information) showing what you're working with and we'll be happy to help you with this.

Thanks,

Ben

Benny Neufeld

Hi, Sorry not replying earlier, must've missed the notification.

I have now included 3 screenshots. The "Product parts list" is where I enter how many parts go into each product. The "Inventory" is where I have all my parts listed in total. And the "Orders" sheet is where I insert when I get a order of a certain product and also the quantity of products. So my goal is that when I insert the product in "Orders" with the "Package SKU" that it will search in the "Inventory" and automatically subtract from each "Parts SKU" the required quantity for a "Package". I hope I explain understandably.

Thanks!

Inventory.png

Orders.png

Product parts list.png

Paul Newcome

Ok. I want to make sure I understand what you are trying to do. Please feel free to correct as needed...

You go to the "Orders" sheet and enter SKU 876273 with a quantity of 6.

You want to then subtract the following from the "Inventory" sheet:

Wheels - 24

Axle - 12

Nuts - 30

Plywood - 30

Lights - 24

5' Wires - 12

8' Metal Tubing - 48

Screws - 90

Which leaves your "Inventory" sheet with (in the same order as above...

26

24

170

50

54

2

52

-5

Or were you wanting to only subtract 6 of each part from the totals on the "Inventory" sheet?

Benny Neufeld

Hello Paul,

Yes! That is exactly what I am looking for. You understood it correct.

Benny Neufeld

I forgot to say which part of your question was right... the first part.

”You go to the "Orders" sheet and enter SKU 876273 with a quantity of 6.

You want to then subtract the following from the "Inventory" sheet:

Wheels - 24

Axle - 12

Nuts - 30

Plywood - 30

Lights - 24

5' Wires - 12

8' Metal Tubing - 48

Screws - 90

Which leaves your "Inventory" sheet with (in the same order as above...

26

24

170

50

54

2

52

-5“

Thats how I would like it.

Thank You in Advance!

Benny Neufeld

https://community.smartsheet.com/discussion/comment/254512#Comment_254512

Hello.

Just wondering... Is that something that would work to create in Smartsheets?

Paul Newcome

My apologies. I thought I had replied. I believe it can be done with some tweaking to your current setup, but I will need to do some further testing to be able to provide a more detailed solution. I'll get back to you as soon as I am able to put something together.

Benny Neufeld

Ok, thank you very much! I’ll be awaiting you response.

Benny Neufeld

https://community.smartsheet.com/discussion/comment/255008#Comment_255008

Good day.

Since it's been a while since you last replied, I was just wondering if you were able to put something together already?

Thanks!