Auto Add/Subtract to Inventory

Options

I am attempting to create an inventory sheet for my business and was wondering if there is a function to automatically add and subtract to a number in a cell. Maybe something similar to say a button that +1 or -1 when clicked. If this is not available what would be the best way for my staff and I to updated inventory quantities quickly.

Answers

  • Genevieve P.
    Options

    Hi @AJ Palmer

    What I would do is actually have a second sheet that uses a Form to populate each row. The form would be very simple, with just the Inventory item or ID to select, then the quantity to + or - (as a text/number cell to populate).

    Which populates a sheet:

    Then you can use a formula in your current sheet to show you the current stock based on what has been added or subtracted in the other sheet.

    This is the formula I'd use:

    =[Original Stock]@row + SUMIF({Item}, [Item Name]@row, {Adjust Stock})

    Would this work for you? There are a few Inventory template sets in the solution center which may have other ideas you could leverage as well.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • AJ Palmer
    Options

    Hello @Genevieve P.


    I think this will work. I have downloaded every inventory template I could find to see if which ones can work for our needs. I have one that I believe will work but need something like this so that our employees can adjust stock levels as items go in an out themselves so we are all aware on our current inventory. One question about this I have is will the formula link the 2 sheets together?


    AJ Palmer

  • Genevieve P.
    Options

    Hi @AJ Palmer

    The formula is using cross sheet references, {these}, to look into your second sheet and SUM together the numbers in one column based on criteria in another.

    So in this formula:

    =[Original Stock]@row + SUMIF({Item}, [Item Name]@row, {Adjust Stock})

    {Item} is the Item column in the intake sheet.

    {Adjust Stock} is the Adjust Stock column in the intake sheet.

    See: Cross-sheet formulas

    Is this what you meant?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • AJ Palmer
    Options

    @Genevieve P. that explains it. Thank you. I will try using this method for my inventory.

  • Jayleen
    Jayleen ✭✭
    edited 02/15/23
    Options

    Hi @Genevieve P. , can this be adjusted to automatically adjust inventory every time someone selects the item every time someone fills out the form with a specific item. In our case, someone may request one specific item multiple times how does it automate in the single row on the original sheet? For example of someone requests -20 item 1s and another requests -30 item 1s how can we ensure 50 is automatically subtracted from the Item 1 in the original sheet?

  • Genevieve P.
    Options

    Hi @Jayleen

    The SUMIF function in the second sheet will SUM together all the values in the {Adjust Stock} column from the intake sheet, based on the Item Name.

    This means if there are two entries in your original sheet with the Item Name "Item 1", then 20 and 30 would be added together:

    SUMIF({Item}, [Item Name]@row, {Adjust Stock})

    If the two values are negative, then -20 + -30 = -50

    So you just need to add the positive number of your Original Stock to the new, negative number:

    =[Original Stock]@row + SUMIF({Item}, [Item Name]@row, {Adjust Stock})


    This is in the second sheet though, not the first. Does that make sense? An alternate way to do this would be to set up a Row Report and then Group by the Item Name, using the Summary function of a Report to gather the numbers together.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Dan Pollino
    Dan Pollino ✭✭✭
    Options

    Good morning,

    This formula worked great for me when I tested it. However, when I deleted the rows used in the test and then entered the item number back on to the blank sheet, it still showed the sum of all the test numbers even though the adjust stock sheet is blank. Any ideas why? Thanks so much!

  • Dan Pollino
    Dan Pollino ✭✭✭
    Options

    Never mind, I refreshed the sheets, and it works fine. Thanks for the help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!