Google-sheets – Reference formulas from one sheet into multiple sheets

The following code will add a menu to the Spreadsheet:

Detective > Trace Dependents

Selecting this will add a note to the active cell with all dependent cell references.

(added search for static references as suggested by Graham below)

You could add a similar function to the traceDependents function to search for the text within the active cell for a Search in Formulas function. I'll leave that as an exercise for you.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = []
  menuEntries.push({name: "Trace Dependents", functionName: "traceDependents"});
  ss.addMenu("Detective", menuEntries);
}

function traceDependents(){
  var dependents = []
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentCell = ss.getActiveCell();
  var currentCellRef = currentCell.getA1Notation();
  var range = ss.getDataRange();

  var regex = new RegExp("\\b" + currentCellRef + "\\b");
  var formulas = range.getFormulas();

  for (var i = 0; i < formulas.length; i++){
    var row = formulas[i];

    for (var j = 0; j < row.length; j++){
      var cellFormula = row[j].replace(/\$/g, "");
      if (regex.test(cellFormula)){
        dependents.push([i,j]);
      }
    }
  }

  var dependentRefs = [];
  for (var k = 0; k < dependents.length; k ++){
    var rowNum = dependents[k][0] + 1;
    var colNum = dependents[k][1] + 1;
    var cell = range.getCell(rowNum, colNum);
    var cellRef = cell.getA1Notation();
    dependentRefs.push(cellRef);
  }
  var output = "Dependents: ";
  if(dependentRefs.length > 0){
    output += dependentRefs.join(", ");
  } else {
    output += " None";
  }
  currentCell.setNote(output);
}

Here is an example of how to do this. When "update" or "edit" is selected from the dropdown boxes, data from the other, designated tab's rows are copied into the current sheet.

Here is the script:

function onEdit(e) {

////UPDATE ROWS WITH DATA FROM REMOTE SHEET VIA SCRIPT
///the script below is adapted from https://mashe.hawksey.info/2015/03/google-sheets-move-a-row-of-data-to-another-sheet-based-on-cell-value/
  // see Sheet event objects docs
  // https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
var ss = e.source;
var s = ss.getSheetByName("Animals List");
var r = e.range;
var remoteSheet = ss.getSheetByName("List B");

  // to let you modify where the action and move columns are in the form responses sheet
var actionCol = 1;

  // Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;

var curNumberCell = s.getRange(rowIndex,2, 1, 1);
var curIDNumber =  curNumberCell.getValue();
var curDCell = s.getRange(rowIndex,4, 1, 1);
var curECell = s.getRange(rowIndex,5, 1, 1);

 // if our action/status col is changed to update or edit, do stuff
if (e.value == "update" || "edit" && colIndex == actionCol) {

 //THE FOLLOWING REPLACES THE FORMULAS OF THE ROW WITH THEIR GENERATED VALUES - UNCOMMENT TO USE:
 //var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
 //sourceRange.setValues(sourceRange.getValues());


///Retrieve data from remote tab/sheet that has matching cell value
var values = remoteSheet.getDataRange().getValues(); //IN REMOTE SHEET WE NEED TO SEARCH ENTIRE SHEET

for (var i = 0; i < values.length; i++) {
   if (values[i][0] == curIDNumber )
   {
   curDCell.setValue(values[i][1] );
   curECell.setValue(values[i][2] );
   }
   }    
} //end retrieve remote data
}