Forum Discussion

danyell723's avatar
danyell723
Copper Contributor
Sep 09, 2020

Automatically Subtract Entered Amount from Existing Value in Another Cell

  In my inventory spreadsheet, cell B18 contains my current raw inventory total.   If a positive value is ever entered into B10 (amount being removed from raw inventory), I want to automatically s...
excel
Formulas and Functions
Bennadeau's avatar
Bennadeau
Iron Contributor
Sep 09, 2020

Hi danyell723,

It would help to know how you get the number in cell B18. 

Let's say that currently, the formula in B18 is the sum of B11 to B17 

=sum(B11:B17)

Now what you want to do is subtract B10 from that previous formula.

=SUM(SUM(B11:B17)-B10)

 

 

  • danyell723's avatar
    danyell723
    Copper Contributor
    Sep 09, 2020

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

    • Bennadeau's avatar
      Bennadeau
      Iron Contributor
      Sep 09, 2020

      danyell723 

      In that case, you will need to write the formula in a different cell.

      Let's say B19 is available, you will write:

      =B18-B10

      If you must have the new value in B18, then invert B18 and B19.

      • danyell723's avatar
        danyell723
        Copper Contributor
        Sep 09, 2020

        Bennadeau 

        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.

         

        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.

Resources