Subtraction Formula for an Inventory On Hand column from 4 source columns
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!
Help Article Resources
Check out the Formula Handbook template!