Subtraction Formula for an Inventory On Hand column from 4 source columns

Mark Poirier

Hi Smartsheet Community. I am hoping that someone out there can help me developed a subtraction formula to automate my inventory consumption that I can incorporate into my Inventory Stock Control sheet. I currently build 4 homes consecutively in my plant all at different stages of production (called Workplans). Each Workplan is numbered from #1 - #38 and have a comprehensive Bill of Material associated with the task. I want to develop a formula to consume the inventory use when the Workplan is completed so this is not a manual task by my Purchaser. Keystroke errors are causing inventory mistakes. The green highlighted columns signify that the workplan is complete and the consumed products are subtracted from the Inventory On Hand but we are doing this manually. Once an email alert notification is generated by the Smartsheet schedule that a workplan is done. my purchase copies and pastes the allocated inventory into it's column from the cost sheet and I want a formula to subtract automatically if the value appears. One other consideration is once the Inventory On Hand formula is incorporated my purchase still needs to adjust the value to enter new stock when Purchase Orders material arrive. See attached pic.

Any help here would be amazing!

Smartsheet Community screenshot.png

MichaelTCA

Hello @Mark Poirier

I can see this being quite a complicated process and it could be easier than I am interpreting right now. I recommend planning a flowchart before tackling this situation.

For estimates, I chose to go a similar route for the estimate numbers (without the criteria whether it's an addition or subtraction). I copy a row to a sheet after a trigger has occurred and there's a column with a function to add 1 to the maximum value to show the next available number.

With another criteria, I can specify whether to add or subtract these values from the sum. It is a linear process so if there was a mistake, you would have to go to the intermediate sheet to update the value.

The intermediate sheet would have a few helper columns to help calculate these values.

It would be hard for me to give a really good suggestion without knowing the ins and outs of how the data is handled and entered within the company you work for. I'm not asking for this information, but that will drive this process, especially if multiple hands are involved.

A column (new or old) to specify whether it's an addition or allocated to a job would be the FIRST requirement.