Sunday, August 26, 2012

How to condition a cell, so that if the value of a cell is higher than a certain number, it will automatically show a certain number or else show that value.

How to condition a cell, so that if the value of a cell is higher than a certain number, it will automatically show a certain number or else show that value.


Here is the script to have this condition on a particular Cell:


function onEdit(e) {

 myFunction();
}

function myFunction()
{
  
  var myNumber = 10; // Set your number which will be used to compare.
  
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName("Sheet11"); // your sheet name
  var range = sheet.getDataRange();
  var CellValue = range.offset(0, 0, 1, 1).getValue(); // Set your Cell position
  
  if( CellValue > myNumber )
  {
    range.offset(0, 0, 1, 1).setValue(myNumber);  
  }
}

Function onEdit(e) will automatically be called everytime you make changes on your sheet.

Note:
You have to set the position of your Cell in Function: "range.offset". First argument is for Row number, second is for Column number, third is for No. of rows (here let it be one because we want to get or set value of only one Cell), and fourth is for No. of columns (let this will also be 1).

So for A1 Cell we will have range.offset(0,0,1,1)
And for A2 we will have range.offset(1,0,1,1)

I hope this will help you having the condition on a particular cell.


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.

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,

No comments:

Post a Comment