Replacing or adding/subtracting values into a cell

BrandoBuenaVista

I've done some researching and trying to wrap my head around this what I thought should be a simple formula.

In my Storage Bin Inventory sheet have 1 Column with the current quantity for any given storage bin. As we start to migrate more into SS, I've added 3 new columns into the sheet that are designed to be "input" cells. If any of the values change in these cells, the row will copy to an inventory log sheet.

What I'm trying to figure out though is how to auto update the Quantity column with an either / or scenario based on what is entered. You may have someone who simply recounts the entire bin and enters that number in, or you may have someone just adding or removing from the bin and entering the quantity removed or added, at no time should there be any date entered into more than of those columns. Is there a way to get all three columns to play nice and sum/subtract (or simply replace) a value in the Quantity column?

Thank you for any help! Still learning some things here.

KPH

Hi @BrandoBuenaVista

• You cannot directly update the Quantity column with a formula that adds or subtracts from the Quantity column - that would create a circular reference.
• You can create a new column and use IF functions to either enter the recount, subtract, or add. See below.
• At this time, you can't update a cell using an automation with a reference to another cell or a formula. So can't set an automation to pull the new total into the Quantity column when it changes.
• You may be able to do something with the row copy you mention but I'd need to know more about what you are doing there.

I suggest that you treat this more like a finance sheet, keeping a running balance that updates based on the recount or qty added or subtracted. This would require a combination of BIN ID and a formula to create the revised count. If this is something you want to do and need help, please ask.

For now, here is a formula to create a 4th column (Revised Qty) with the total after the changes.

=IF(ISBLANK([Total Recount]@row), IF(ISBLANK([Qty Used]@row), IF(ISBLANK([Qty Added]@row), "", Quantity@row + [Qty Added]@row), Quantity@row - [Qty Used]@row), [Total Recount]@row)

I will build it slowly to show how it works. If you don't need a rolling total this could be enough. If you do need a rolling total, we will use this to build it, while also matching on the BIN ID.

Start with the formula to bring the recount over. Use ISBLANK. If this is true the placeholder text is displayed. If false the new count is returned. Like this:

=IF(ISBLANK([Total Recount]@row), "another formula here later", [Total Recount]@row)

Next a formula for if Qty Used is blank and a calculation if not. This replaces the text string in the first formula (and is shown in bold here).

=IF(ISBLANK([Total Recount]@row), IF(ISBLANK([Qty Used]@row), "another formula here later", Quantity@row - [Qty Used]@row), [Total Recount]@row)

Then a formula for if Qty Added is blank and a calculation if not. Again, this replaces the text string. Here I have used "" to do nothing for any row where all three columns are blank.

=IF(ISBLANK([Total Recount]@row), IF(ISBLANK([Qty Used]@row), IF(ISBLANK([Qty Added]@row), "", Quantity@row + [Qty Added]@row), Quantity@row - [Qty Used]@row), [Total Recount]@row)