0

I am trying to make a functionality that allows me to enter a value into a cell and have that value automatically be multiplied by another cell, overwriting the original entry.

For example, if Cell A2 has 5 inside it and I am entering 10 into cell C2, I would like it to automatically multiply A2 by the value I have just entered in C2 and overwrite the ‘10’ I have just written in Cell C2 by 50 which would be the answer to this calculation.

I'm still very new to script editor so below is the best I've been able to do.

function onEdit(e) {
  var Column1 = 1
  var Column3 = 3;
  if (e.range.getColumn() == Column3) {
e.range.setValue(Column1 * Column3)
} 
}

I've also tried the script below without any luck.

function onEdit(e) {
 var sheet = SpreadsheetApp.getActiveSheet();
 var dataRange = sheet.getRange("A:C");
 var data = dataRange.getValues();
   for (var i = 0; i < data.length; ++i) {
   var rowData = data[i];
   var Column1 = rowData[0];
   var Column3 = rowData[2];
   if (e.range.getColumn() == Column3) {
   e.range.setValue(Column1 * Column3)
   }
   }
   }

I appreciate your feedback.

1
  • When the value is put to the column "C", You want to put the value multiplying the value of the column "A" to the value of the column "C" to the column "C".
  • You want to achieve this using OnEdit event trigger.

If my understanding is correct, how about this modification? I modified your above script.

Modified script:

function onEdit(e) {
  var Column1 = 1
  var Column3 = 3;
  if (e.range.getColumn() == Column3) {
    e.range.setValue(e.value * e.range.offset(0, Column1 - Column3).getValue());
  } 
}
  • e.value is the put value to the column "C".
  • e.range.offset(0, Column1 - Column3) is the column "A" of the same row of the column "C" that the value is put.
  • e.range.offset(0, Column1 - Column3).getValue() is the value of column "A".

Note:

  • This is a simple modification. So please modify it for your situation.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

  • When you delete a value from the column "C", you don't want to run the script.

From your reply comment, I could understand like above. If my understanding is correct, how about this modification?

Modified script:

function onEdit(e) {
  var Column1 = 1
  var Column3 = 3;
  if (e.range.getColumn() == Column3 && e.value) {
    e.range.setValue(e.value * e.range.offset(0, Column1 - Column3).getValue());
  } 
}
  • Hello!! This is amazing, I've been trying to resolve this for weeks now so your help is a life saver. I also really like the bullet point explanation you've given for the formula at the bottom of your answer, but could you expand on it a little bit more as i'm not sure I quiet understand it. If it is not too much to ask of you? – j_kazmi Jun 18 at 15:22
  • @j_kazmi Thank you for replying. I'm glad your issue was resolved. But I couldn't understand about your additional comment. I apologize for my poor English skill. If my answer is incomplete for your issue, I have to apologize. – Tanaike Jun 18 at 22:28
  • I was simply asking for an extended explanation of the last line of the script and how it works but it is ok. I have another issue with the formula which I would be grateful if you look at. So If I enter a number into column C it multiplies it with the corresponding number in column A, which works perfectly as expected. However, when I try to remove a number from column C it returns a '#NUM!' error. Is there a way for it to return a blank cell instead of this error. Thank you for all your help so far. – j_kazmi Jun 19 at 13:54
  • @j_kazmi Thank you for replying. I couldn't notice about the situation of delete the value. This is due to my poor skill. I deeply apologize for this. I updated my answer. Could you please confirm it? If that was not the result you want, I apologize. – Tanaike Jun 19 at 23:39
  • Thank you so much for assisting me. Now this function works exactly as I wanted. And there's no need apologise, you've helped a lot! One last question for you. If I want this to happen for all columns from C to J do I simply just copy the IF statement and change it so it works for 'Column4', Column5', Column 6' etc. Thanks! – j_kazmi Jun 20 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.