HELP - How to do Inventory Tracking

Paul Rosas

The picture at the top is the form that I would like to utilize to have people fill out when they take an item out of our inventory. The bottom photo is a representation of what my sheet currently looks like. I know the form and the sheet don't represent the same columns, and that is because the form attachment is from a separate sheet. (I need to track the name of the person taking an item, but I don't want it auto populating random rows with employee names at the bottom of my inventory sheet if I can avoid it)

I want to know if it is possible to have someone fill out a form similar to the one above, and have it automatically reflect which item and quantity was taken on the sheet itself. For Example, if John Doe takes 2 "Apple" product items out of inventory, he specifies it on the form, says that he took 2, gives us his name and the date, and then as soon as the form is submitted, the stock quantity for "Apples" reflects with 2 less than it had before, automatedly.

Is this possible? If not, please give me a clean alternative solution.

Thank you.

Genevieve P.

Hi @Paul Rosas

Yes, this is possible! You would need to have the initial quantity somewhere in the sheet, then you can use a formula to SUM together the total taken from the Form Sheet, and subtract this number from the original quantity.

For example:

=[Stock Quantity]@row - SUMIF({Item Name Other Sheet}, [Item Name]@row, {Quantity Column Other Sheet})

Does that make sense? Here are some resources that may help you:

• SUMIF Function
• Formulas: Reference Data from Other Sheets
• Create Efficient Formulas with @cell and @row

Cheers,

Genevieve