Auto Add/Subtract to Inventory

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.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin

    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

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