I'm creating a stock tracker in smartsheet and need help implementing functions to count quantities

santiago.rendon

I have around 20ish food and drink items we want to keep track of. I have figured the easiest way to do it is making a column have a dropdown list of the items you can choose from, next column being the added stock of inventory that gets delivered, next column is removing the stock when it gets used.

the added stock updates the quantity and then the removed stock updates the total quantity since if i had only one column of the quantity it would make a circular reference.

now the last column I wanted to see if I could calculate the items total quantities by adding the column using IF and HAS functions but I can't seem to get it to work.

If anyone has any ideas of how I can do this please let me know, or if anyone is confused I can try to go more indepth for it

Brian_Richardson

I'd maybe set it up a little differently, so it's not as confusing where to add "removed" items. The way it is now, you have to look at a row for water or snack, then eyeball the added amount, then know to edit the removed.

If you set up two sheets, I think this would go more smoothly:

1) Inventory Sheet: List of each unique item you're tracking, with columns for "Total Added", "Removed", and "Total Remaining". Could also add a "Order More" flag to automate even more :-)

2) Inventory Additions Sheet: Sheet where new inventory is added each time. The "Type" column is a dropdown list of your 20 items. You can also setup a form on this sheet and give that form to people to fill out, then Smartsheet will automatically add the data when the form is submitted.

3) Now on the Inventory Sheet you can add some formulas to make this all work.

For "Total Added" use a cross-sheet reference to sum up all the additions from the Additions sheet. If you haven't used a cross-sheet reference before, you do it by typing in the formula and click "Reference another sheet" in the popup box as you type it. Then select the column on the other sheet that you're referencing. That will insert a reference with {} brackets.

= SUMIF ( {Inventory Additions-Type} , Type@row , {Inventory Additions-Quantity Added} )

Once you enter the formula in one of the Total Added cells, right click it and choose "Convert to Column Formula". This will apply the formula to the entire sheet.

By setting it up this way, you can have multiple entries for added inventory, that will sum together. You can also use a form for inventory additions. Share the Inventory sheet to your team and they can also fill in the "Removed" amount as they use inventory, or you could replicate removals with a third sheet and the same type of formulas as Additions if you wanted to give people a form to fill out for removals.

Finally, you can set formulas in the "Order More" flag to look for a Total Remaining <5 or something. Then setup an automation to alert you when the flag gets set.