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

Azley

How do I recreate the sheet & form you have created?

Genevieve P.

Hi @Azley

I would suggest taking a look at the templates in the Solution Center to see if any of those have a set-up you could use.

https://app.smartsheet.com/r/create

For example, the Request Intake & Tracking or Asset Management

Cheers,

Genevieve

Azley

@Genevieve P. thanks, I have created a form that when our team removes an item from our warehouse that have to fill in what item, how many, and a few other things. I want that sheet to speak back to our master inventory sheet and auto update the correct row can you provided some guidance on how I can create this link

Genevieve P.

Hi @Azley

Depending on your set-up, you could use cross-sheet formulas to populate columns in your master inventory sheet. For example, you could use a SUMIFS formula to SUM the quantity of a specific item listed in your intake sheet:

=SUMIFS({Quantity Column}, {Item Column}, [Item]@row)

See: SUMIFS Function

Here are some other article references that may be helpful:

• Create cross sheet references
• Formula combinations for cross sheet references

The types of formulas you use will depend on what you need to bring back and how your sheets are organized. IF you'd still like help, I would recommend creating a new Question type of post here in the Community with screen captures of both sheets (blocking out sensitive data) and an explanation of what you need synced or linked.

Cheers,

Genevieve