Automatically Subtract Entered Amount from Existing Value in Another Cell

Question

&nbsp;In my inventory spreadsheet, cell B18 contains my current raw inventory total.&nbsp;If a positive value is ever entered into B10 (amount being removed from raw inventory), I want to automatically subtract that amount from B18. I've been doing this manually for years, but sometimes I will forget to update the new total and then inventory becomes a nightmare!&nbsp;I have a feeling that this is a very simple formula, but I just can't find the right way of doing it without creating a Circular Reference!&nbsp;PLEASE HELP!&nbsp;&nbsp;

bennadeau · Answer

Hi&nbsp;danyell723,It would help to know how you get the number in cell B18.&nbsp;Let's say that currently, the formula in B18 is the sum of B11 to B17&nbsp;=sum(B11:B17)Now what you want to do is subtract B10 from that previous formula.=SUM(SUM(B11:B17)-B10)&nbsp;&nbsp;

danyell723 · Answer

BennadeauThere's no formula associated with B18. I manually entered that value and have been revising it as needed.

bennadeau · Answer

danyell723&nbsp;In that case, you will need to write the formula in a different cell.Let's say B19 is available, you will write:=B18-B10If you must have the new value in B18, then invert B18 and B19.

danyell723 · Answer

Bennadeau&nbsp;Ok, so THAT'S the part I'm missing I guess....the inverting.I know I've been talking about a single cell, but it's actually an entire row of inventory totals (B18-Z18) that would need this formula.&nbsp;So I entered that formula into B17, dragged to copy it across the entire row, and now I just don't know how to invert this to where B17 remains blank (albeit the formula), but the values get transferred to B18? And more so, how to keep it automatically doing so.

bennadeau · Answer

danyell723&nbsp;By inverting I meant input your data in B17 and your formula in B18.If you absolutely don't want to see the original data (in B17 in your case), you can format the cell so the background and the text is the same color. That's a "cheap" way to do it but it would work.If you can share your spreadsheet (assuming there's nothing confidential in it), I'd like to see if I can take a different approach. I'm mostly curious to know how you get the data in row 18.

Forum Discussion

Automatically Subtract Entered Amount from Existing Value in Another Cell

8 Replies

Resources