Saturday, September 28, 2013

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:
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,

1 comment:

  1. Hello Kishan!

    First, let me thank you for taking the time to answer a burning question for many google sheets users! It's frustrating to wade through the pages to find an appropriate answer, which you are!!

    Also, I wanted to ask a question that would expound on the first answer. I've started studying Python and am really trying to wrap my head around the orders of operations.

    1. So we have a cell that deducts a specified number (A) from a specified cell's number (B). Now how would we alter the script to wait on a "Go" cell. Lets say you have 5 cells that all perform this function, and in order to make it go A2=1.

    2. Once the script is performed, how would we write in for the script to reset A2 to 0, and then clear all the other cells that contained input (The A's)

    Thank you for your time and help. The more I learn about this the more my eyes are widening and I'm really enjoying myself! Thank you for your patience! And have a wonderful 2015!

    Sincerely,
    George

    ReplyDelete