Inventory Management

Josh Canady
Josh Canady ✭✭✭
edited 03/03/20 in Formulas and Functions

I'm trying to find a way to remove inventory when an order is sold, other than manually changing the numbers. I have a form set up to add new orders to the bottom of the sheet. I need a formula to automatically remove the inventory items from each line in the Sold column. Example:

Customer #1 bought 5 items of my Stock #2. I need it to change the 0 in the Sold column to 5 on line 3.


It also needs to be able to update when another order is added. Example:

Customer #3 bought 35 items from Stock #2. So now the 5 would be changing to 40 (in the Sold column on line 3).

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. So to pull a total of all items removed for Stock #2...

    =SUMIFS(color:color, [WH Location]:[WH Location], [Stock #]@row)


    Then we subtract that from your starting stock and that should do it for you.

    =[In Stock]@row - SUMIFS(color:color, [WH Location]:[WH Location], [Stock #]@row)


    NOTE: This is assuming you are putting the formulas in the [Remaining Stock] column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!