Can Conditional Formatting trigger specific formulas?

The spreadsheet in question is a shipping/inventory database. 

Screen Shot 2020-03-20 at 11.40.11 AM.png

The top rows where Device SKUs are listed are the total contracted amount of devices a client pays for. Then as we create shipments (which go under the blue parent rows), we've included a SUMIF formula that, when the Device SKU is entered in the shipment description, the "Remaining Quantity" updates and keeps an accurate count. SUMIF formula pasted below:


=Quantity@row - (SUMIF([Device Sku]:[Device Sku], [Device Sku]@row, Quantity:Quantity) - Quantity@row) + [Spare Devices]@row


The problem that arose yesterday is to do with Change Orders. So, as the name suggests, a change order is when a client wants to add or remove devices. It's important we keep track of this AND that we maintain an accurate count in relation to "Remaining Quantity". Please see below for the Change Order columns:

Screen Shot 2020-03-20 at 11.40.16 AM.png

As you can see in the screenshot above, the client has added 10 more "On-Off Switches" and then removed 5 "On-Off Switches". Depending on what device the client wants to change will determine where the CO rows are added. In this case, it's the "On-Off Switch" and they now live as child rows underneath the original. 

I do not know if Conditional Formatting offers this functionality, but, what I was hoping to do was that when a quantity is added to "Change Order Quantity" then the "Type of Change" is chosen, that triggers a formula (Add or Subtract) that updates the total quantity for the device. I've highlighted the cells I would like talking to each other in brown below:​

Screen Shot 2020-03-20 at 11.39.48 AM.png

Totally understand if this is not at all possible or if anyone in the community has any suggestions to visualize/calculate the change orders in relation to total quantity and remaining quantity. It's also possible that I'm automating this spreadsheet too much or I've been staring at it too long 😬

This community is awesome. Thank you!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!