Set value based on a cell in other cells

Question:

Still having some spreadsheet problems. What I am trying to do is...
if A1 is A, then I want B1 to be X, C1 to by Y. However, I often change the date in B2,C3 so I dont want the formula based there so that I dont erase the formula when altering data.  I would like the formula to be based in A1 so that if A1 is A then it makes corresponding changes to several other cells in different columns.  I know the below formular dosent work but it conveys what i am trying to do.

If A1=A, then I want B1=L, and C1=S, and D1=C.  Is this possible?  Is there a way to lock the formula so that if I make a change to a single data point it dosent erase the entire formula?  Thanks.

Solution:

You can't do that with formulas, if you wish you can do it with scripts.

Here is the sample script for achieving what you want:


function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet10");
  
  var activecell = s.getActiveCell().getA1Notation();
  if (activecell == "A1")
  {
    var a1 = s.getRange("A1").getValue();
    
    if (a1=="A")
    {
      s.getRange("B1").setValue("X");
      s.getRange("C1").setValue("Y");
    }
  }
}

Now, put the above script in the script editor. And now whenever you will put "A" in Cell A1 you will get "X" in Cell B1 and "Y" in Cell C1.


And If you are not much familiar with scripts then check out the following link:

http://igoogledrive.blogspot.in/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 or confidential projects:

http://igoogledrive.blogspot.in/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