Can Conditional Formatting trigger specific formulas?
The spreadsheet in question is a shipping/inventory database.
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:
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:
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:
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!