Google-sheets – Stop circular dependency calculations unless a specific cell is edited

google sheets

I am trying to figure out how to get Google sheets to only calculate circular dependency when a specific cell is changed. What is happening now is that the calculation occurs every time anything is changed on the sheet.

What is happening now is that if A1 is supposed to add B1 to itself, it works. But, if I then change the value in B2, A1 adds B1 to itself again. Grrr. Very frustrating. Again, I only want A1 to add itself to B1 if B1 is changed.

And yes, I have circular dependency enabled.

Example formula placed in A1: =A1+B1

Best Answer

This is not possible but this could be done by using an on edit / on change Google Apps Script's trigger.

Example:

function onEdit(e){
  var sheet = range.getParent();
  if(range.columnStart === 2 && range.rowStart === 1) {
  sheet.range('A1').setValue(sheet.range('A1').getValue() + e.value);
}

Related Solutions

Google Sheets Formulas – Fixing Circular Dependency with IFERROR

The pure spreadsheet answer is to create columns D and E to display your information. An example row:

A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )

The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the JavaScript would detect the edit to B3 and automatically add the formula in C3, or vice versa.

Google Sheets – How to Execute Function Only Once to Prevent Circular Dependency

With formula's it can't be done, as per your other question, but with a little piece of Google Apps Script you can roughly accomplish what you want.

Code

// global
var app = SpreadsheetApp.getUi();

function onOpen() {
  // create menu entry
  app.createMenu("IDs").addItem("Set all IDs", "myIDs").addToUi();
}

function myIDs() {
  // set variables
  var sh = SpreadsheetApp.getActive();  
  var rText = app.prompt("Give range", "Like A1:A10 (single column only)", app.ButtonSet.OK_CANCEL); 
  var r = sh.getRange(rText.getResponseText()), v = r.getValues(), max = 0;

  // retrieve max value
  for(var i = 0, iLen = v.length; i < iLen; i++) {
    var value = v[i][0];
    if(value > max) {
      max = value;
    }
  }

  // prepare output
  var output = [];
  for(var j = 0, jLen = v.length; j < jLen; j++) {
    var val = v[j][0];
    if(val == "") {
      output.push([max + 1]);
      max++;
    } else {
      output.push([val]);
    }
  } 

  // show/return results
  r.setValues(output);  
}

Explained

From the menu, the script is activated. A prompt is shown and a range is requested. The script will retrieve all data from that range and find the highest value. Afterwards it will evaluate the range for empty entries. Once found, it will add a value (max + 1) to that position and re-set the maximum value. At the end of the script all data is added new data to the spreadsheet.

Screenshot

before & after
enter image description here

Example

I've created an example file for you: check if cell itself is empty
Add the script under Tools\Script editor from the menu. Press the bug button to authenticate the script.