Google Spreadsheet How do you subtract/add values from a cell much like depositing and withdrawing from a bank account? (Not just + and - operators)

Question:

( by Tes Lescat )

Say I have a cell (let's say A1) with value 1000

From another cell (B1), I want users to input X so that A1 will become 1000+X. From that same cell B1, if a user were to input -Y, A1 does not go back to 1000 but rather adds or subtracts all the values that have ever been entered into A1. Thus, A1 would be 1000+X-Y

For example:

A1 = 500

user enters B1 = 4

A1 = 504

user enters B1 = -20

A1 = 484 (because 504-20, not 500-20)

user enters B1 = 100

A1 = 584

etc. etc.

Solution:

Have a look at the following animated screenshot:

Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan
// For more visit: iGoogleDrive.blogspot.com

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var a1 = s.getRange("A1");
  var b1 = s.getRange("B1");
  var activeCell = s.getActiveCell().getA1Notation();
  if( s.getName()=="Sheet Name" && activeCell=="B1" )
    a1.setValue(a1.getValue()+b1.getValue());
};

///////////////////////////////////////

put the above code in your script editor and then whenever you edit on any sheet, it will automatically update the current total in Cell "A1" of the sheet name "Sheet Name".

You can change the "Sheet Name, "A1" and "B1" as per your requirement.


And If you are not much familiar with scripts then check out the following link:http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive