Auto Add/Subtract to Inventory

AJ Palmer

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.

Genevieve P.

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

AJ Palmer

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.

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

AJ Palmer

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

Jayleen

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.

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

Dan Pollino

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

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