Can Conditional Formatting trigger specific formulas?

Mark Silvester

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:

[email protected] - (SUMIF([Device Sku]:[Device Sku], [Device Sku]@row, Quantity:Quantity) - [email protected]) + [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:

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!

Paul Newcome

So [Total Quantity]1 should be 201 because you are adding 10 and removing 5? Am I reading that right?

My initial thought is that it can be done. I just need to better understand exactly how you want everything to work together (I may have been staring at my computer too long today too).

Mark Silvester

Hey Paul,

Thanks for your help! That's correct, I haven't added or subtracted the 10 and the 5 in the examples above, but yes, the "total remaining quantity" after the two change orders would be 201.

Still can't wrap my head around a way for that to happen automatically. The process (in my head) would be:

1. Input the qty in "Change Order Qty"
2. Select "Change Type"
3. Total qty adjust accordingly

Happy to manually add/subtract but would be very satisfying if it was automated. 😎

Paul Newcome

Ok. SO this is actually pretty straightforward if we tackle it in pieces....

We want the total number of Add's:

=SUMIFS([Change Order Quantity]:[Change Order Quantity], [Type of Change?]:[Type of Change?], "Add")

And the total number of Remove's:

=SUMIFS([Change Order Quantity]:[Change Order Quantity], [Type of Change?]:[Type of Change?], "Add")

Then we add the Add's and subtract the Remove's (if there aren't any, then the formula(s) will return 0 which won't affect the total anyway).

=original total quantity formula + Add's formula - Remove's formula

=original total quantity formula + SUMIFS([Change Order Quantity]:[Change Order Quantity], [Type of Change?]:[Type of Change?], "Add") - Remove's formula

=original total quantity formula + SUMIFS([Change Order Quantity]:[Change Order Quantity], [Type of Change?]:[Type of Change?], "Add") - SUMIFS([Change Order Quantity]:[Change Order Quantity], [Type of Change?]:[Type of Change?], "Add")

Mark Silvester

Magic!!!! Thank you so much Paul.

The only issue I'm having now is that the original formula is subtracting the "Spare Devices Quantity" from "Total Quantity" instead of adding it (I've gone through and removed every other mention):

"Remaining Quantity" should be 200, instead of 192... 🤔

=[Floor Plan Quantity]@row - (SUMIF([Device Sku]:[Device Sku], [Device Sku]@row, [Floor Plan Quantity]:[Floor Plan Quantity]) - [Floor Plan Quantity]@row + [Spare Device Quantity]@row) + SUMIFS([Change Order Quantity]:[Change Order Quantity], [Type of Change?]:[Type of Change?], "Add") - SUMIFS([Change Order Quantity]:[Change Order Quantity], [Type of Change?]:[Type of Change?], "Remove")

You've already helped me so much, so I can totally try and figure this one out.

Thanks again!

Paul Newcome

No worries at all. All we have to do is use a set of parenthesis so that your first formula runs first and the second part runs second

=(original formula) + Add's - Remove's

=([Floor Plan Quantity]@row - (SUMIF([Device Sku]:[Device Sku], [Device Sku]@row, [Floor Plan Quantity]:[Floor Plan Quantity]) - [Floor Plan Quantity]@row + [Spare Device Quantity]@row)) + SUMIFS([Change Order Quantity]:[Change Order Quantity], [Type of Change?]:[Type of Change?], "Add") - SUMIFS([Change Order Quantity]:[Change Order Quantity], [Type of Change?]:[Type of Change?], "Remove")