Auto increment just one cell only when condition meets

Question

How do I auto increment one cell only (A1 for e.g) if A2 meets the condition? Here is an example.

If A2 = 2, A1+150 and keep that new value even if A2 changes back. But once A2 = 2 happens again, I want a newly added value A1 to be added another 150 again. This time A1= A1 + 150 + 150 after A2=2 happens for 2nd time. Can be be done through just spreadsheet or need a google script? or is there a better idea to auto add the Cell A1.

create trigger on change, if previous A2 <> 2 and current A2 = 2 then A1=A1+100 — user11982798

Benoît Wéry Benoît Wéry · Accepted Answer · 2020-01-16T14:54:51

Some more details on user11982798's comment: you can use the onEdit() trigger for this. See https://developers.google.com/apps-script/guides/triggers.

This code will probably do what you need:

function onEdit(e){
  if (e.range.getA1Notation() == "A2" && e.oldValue != 2 && e.value == 2) e.range.getSheet().getRange("A1").setValue(e.range.getSheet().getRange("A1").getValue() + 150);
}

In case you want to refine further the conditions (e.g. only do so for a certain sheet, or for several cells,...) check the documentation of the event object here: https://developers.google.com/apps-script/guides/triggers/events

... and of course the overall Spreadsheet App Script documentation.

Auto increment just one cell only when condition meets

1 Answers