How do I get Google Sheets to automatically subtract a value from one cell by entering the value in another?

Question

I have a budgeting spreadsheet on Google Sheets which I’d like to be able to do two things (the first, I think, is easier than the second and the first is the priority for me):

1. I write a value in, say, cell A1 of income I am expecting from a certain source that month. When all or some of that income hits my bank account, I’d like to be able to write that amount in, say, cell A2 and have it automatically subtract from what’s in A1. So say I’m expecting to earn £1000 from a certain client, I record £1000 in A1. Then I’m paid £600 (with the rest to follow later). I’d like to be able to write £600 in A2 and A1 automatically update to £400.

2. If poss I’d also like to be able to make cells A1 and A2 sum any subsequent figures I put in there and still subtract A2 from A1. So, for example, I’d like to be able to add figures over time into cell A1 and have those figures sum up. Then I’d like to be able to do the same to cell A2 and for those figures to sum up too, and then subtract from what’s in A1

So imagine I had £1000 in A1 and then I do some more work for that client and so am expecting a further £500. So I then overtype on the £1000 in A1 with “500” and the cell returns a value of £1500. Then I get paid £600 for one bit of work from the client, write £600 in A2 and A1 automatically returns a value of £900 (per the process in point 1 above). Then I earn another £400 from the client, overtype on £600 in A2 and so that cell returns a value of £1000, and A1 automatically updates with a value of £500.

Answer 1

You want to use two cells to track the balance owing (Cell A1) and the total amounts paid (Cell2) by clients.

It should be noted this approach is fraught with danger since any mis-type may have the effect of rendering the cumulative values of Cells A1 and A2 incomprehensible.

---

Why your script doesn't work.

Two reasons:

• first, there can only be one onEdit() trigger, whereas you have two functions named onEdit. This is not a serious hurdle since the two functions can be easily combined. In fact they must be combined, since the a customer receipt not only updates cell A2, but also updates Cell A1.

• second, the scenario dictates that the same cell must be used for data entry as well as the cumulative balance. The value of data entry is easily determined - it is the value of the cell. The cumulative balance is the sum of the amount entered PLUS/MINUS the previous balance. However, depending on the transaction, it is not possible to use getValue() for the previous balance since getValue() will return only the current balance. Fortunately, Event Objects come to the rescue - an object named oldValue is available when a cell has a value prior to data entry. So the cumulative balance can be calculated as the sum of the value plus the oldValue

---

The following answer uses a simple onEdit trigger and Event Objects. At the outset, there is no value available for oldValue, so this is tested to determine if it is undefined; if yes, then a value of 0 (zero) is assigned.

Since the script relies on values in Cells A1 and A2, there is no way to halt the script; even deleting a value does not help. I took the view that this was not a positive outcome, and added a test on Cell A3 - any text entered in this cell will stop the script.

---

function onEdit(e)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //Logger.log(JSON.stringify(e));
  
  // variables
  var sheetname = "Sheet1";
  // A1 = Income
  // A2 = Payments
  // A3 = enter a word here to reset the script
  var income = "A1";
  var payments = "A2";
  var endcode = "A3";
  
  // get Event Objects
  var editRange = e.range;
  var sheet = editRange.getSheet();
  
  // define ranges
  var incomeRange = sheet.getRange(income);
  var paymentsRange = sheet.getRange(payments);
  var endcodeRange = sheet.getRange(endcode);
  
  // trigger to End the code cycle
  if (sheet.getName() === sheetname && editRange.getA1Notation() === endcode){
    incomeRange.setValue("");
    paymentsRange.setValue("");
    endcodeRange.setValue("");
    //Logger.log("end the routine");
    return false;
  }
  
  // test for income
  if (sheet.getName() === sheetname && editRange.getA1Notation() === income)
  {
    //Logger.log("the edited range is "+income)
 
    // test for oldvalue = undefined
    if (typeof e.oldValue === 'undefined') {
      var OV = 0;
      //Logger.log("old value = undefined so adjusted old value = "+OV);
    }
    else
    {
      var OV = Number(e.oldValue);
      //Logger.log("old value = "+OV)
    }
    
    // test for new value = undefined
    if (typeof e.value === 'undefined'){
      var NV = 0;
      //Logger.log("new value = undefined so adjusted new value = "+NV)
    }
    else
    {
      var NV = Number(e.value);
      //Logger.log("new value = "+NV)
    }

    var newValue = OV+NV;
    //Logger.log("Summary: OV:"+OV+", plus NV:"+NV+" equals newValue:"+newValue)
    incomeRange.setValue(newValue);
    //Logger.log("updated A2");
    return false;

  } // end update income
  
   // test for payments
  if (sheet.getName() === sheetname && editRange.getA1Notation() === payments)
  {
       
    Logger.log("the edited range is "+payments)
   
    // test for oldvalue = undefined
    if (typeof e.oldValue === 'undefined') {
      var OV = 0;
      //Logger.log("old value = undefined so adjusted old value = "+OV);
    }
    else
    {
      var OV = Number(e.oldValue);
      //Logger.log("old value = "+OV)
    }
    
    // test for new value = undefined
    if (typeof e.value === 'undefined'){
      var NV = 0;
      //Logger.log("new value = undefined so adjusted new value = "+NV)
    }
    else
    {
      var NV = Number(e.value);
      //Logger.log("new value = "+NV)
    }

    var newValue = OV+NV;
    //Logger.log("Summary Payments: OV:"+OV+", plus NV:"+NV+" equals newValue:"+newValue)

    paymentsRange.setValue(newValue);
    //Logger.log("updated B2")
    
    // update Income outstanding
    var incomeValue = incomeRange.getValue();
    var adjIncome = incomeValue - NV;
    //Logger.log("Summary: income value before adjustment:"+incomeValue+", less payment value:"+NV+" equals adjIncome:"+adjIncome)
    incomeRange.setValue(adjIncome);
    //Logger.log("updated B2")
    return false;
  }
  
return false; 
}