Using a formula to subtract 1 box out of "box inventory" when I record a sale in "Sales"

Josh Fedorchak

Hello, 

We have a basic retail business selling iPhones and what I would like to do is create some type of formula so when I enter a sale into my Sales Sheet it will link to my Box Inventory Sheet AND it will subtract the correct box out of the inventory based off of its Model and Color. 

I'm not any good with creating formulas. Can someone please help with some detailed advice and please use Layman's Terms haha!

 

Essentially what I want to do is something like an IF THEN formula....

For Instance...IF we sell an "iPhone 7 Plus - Rose" THEN "iPhone 7 Rose" on my Box Inventory Sheet will subtract by 1.

 

the first picture is an example of our sales page with the model of the phone and the color that will match the box that we ship out. I want my Box Inventory (2nd Photo) to automatically subtract by 1 each time we post a sale 

Thank you!

Adam Overton

Consider adding another column to track how many are outstanding, and then another for net inventory:

Inventory    Encumbered    Net Inventory

5                 3                        2

Net inventory row 1 is the formula =[Inventory]1 - [Encumbered]1