Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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


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!

Screen Shot 2017-05-05 at 1.06.17 PM.png

Screen Shot 2017-05-05 at 1.04.16 PM.png


  • 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


This discussion has been closed.