Inventory Management

I'm looking to create a sheet to manage our department's inventory of swag/give-aways. I currently have the asset management template set up and running and am using the barcode functionality which is mostly serving our needs. The one gap I have is inventory updating. Ideally, when someone takes inventory, I would like for them to indicate quantity taken in one column, have the remaining quantity column be updated accordingly, and then have the quantity taken column clear for the next person. I'm using a simple formula (original quantity column-quantity taken column=quantity remaining column) and then a workflow to clear the quantity taken column. The problem being of course, as soon as the quantity taken column is clear, the formula sees that column as a zero and the remaining quantity column reverts back to the original quantity value.

Is anyone else tracking inventory in a similar way? Or have advice on a different formula/workflow to use? Thank you!


  • JamesB
    JamesB ✭✭✭✭✭

    @tori.ives_AimsCC You could through Automation when the Quantity taken column is not blank copy that row to a new sheet, and then have another automation clear the quantity taken cell. Then in you quantity remaining column have your formula reference the sheet that you are copying rows to, to manage the deduction of the available quantity. This will require the user to save the sheet after they have updated the quantity taken column.

    Another option would be to create a separate sheet with a form that users could submit with the quantity taken and then have a formula to reference that sheet to update the remaining quantity on your main inventory sheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!