Rolling and Automated Inventory

mcreexna

Hello Smartsheet Community!

I've been attempting to set up a rolling and automated inventory and have ran into a mind splitting concern. I've been able to set up a Master Inventory, Incoming/Outgoing sheet, and a Rolling Data sheet to speak to one another with notifications to notify when a currently stocked item needs to be ordered, the problem I am having is setting up either an automation or formula that will update the "Current Stock" number any time that an item is taken out of or added to the stock.

To explain the conundrum, lets say on Monday I have 10 of item A in current stock. A log in the rolling data provides that 7 of that item has been removed and an order to refill has been submitted due to notification need. On Friday, we received 15 of Item A and logged into the rolling data. The rolling data sheet speaks to the Incoming/Outgoing sheet with the most recent updates when items are removed or added while the Incoming/Outgoing speaks to the Master Inventory (little bit of a middle man). The issue I'm running into is that the data will provide the sum of all of Item A that has either been removed or added on the Rolling Data sheet or (utilizing a SUMIFS function) set a date parameter to only provide the sum of amounts added or removed for Item A for "Today()". Either way, when used in a simple function of Current Stock +/- Added/Removed Stock will result to trigger to include the total sum or use the data available within the date parameter.

Is there a way to run the formula only once for the day to update the Current Stock number and keeping the updated number without influence of removing or a total sum of data on the Smartsheet platform, or will this require manual entry maintenance?

Thank you anyone in advance that is able to provide assistance!

Jgorsich

I'm having trouble parsing your question, but it seems like you used some automation where a formula would have been better. The way I'd set up this system is like this:

Inventory Log Sheet: a dated, ongoing, entry of inventory changes as they occur. Any given entry would be a single row indicating the date, item, and number entering or leaving inventory (use either positive and negative numbers or different columns for entering vs leaving). Entries to this can be manual or from a form.

Current Inventory Sheet: a list of all items and their current inventory levels. Current inventory levels would be captured with a sumifs() formula that adds all additions to inventory for that item from the log sheet minus a second sumifs() formula that adds all subtractions to inventory for that item from the log sheet. There would be no date restrictions in these formulas - they'd be ongoing and if they gave a number that was different from the reality of what you have in stock you'd enter a correction entry to add or remove items into your log sheet.

Various summary reports and sheets: used to give you views of how inventory changed over any given time period.

So - per your example - on Monday you buy 10 of item A, this creates a single row in your log sheet and makes the count of A in your Current Inventory Sheet go up by 10. On Tuesday you sell 15, this creates a new row in your log and your current inventory sheet how reflects 5 less then it did at the start of the week (10-15).