1

I have a sheet that makes extensive use of cells from another sheet with formulas, some of which have errors. The formulas look like:

=SHEETA!$E$568

Is there a way, or a script, that can parse the formula and either let me jump to, view, or in another tab, highlight cell SHEETA!$E$568?

Desktop Microsoft Excel once had a feature that drew lines from the source cells to the formula: this would be a similar use case.

1 Answer 1

1

Try this script. To access it, either put it in a custom menu or create a shortcut.

function jumpto() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var ui = SpreadsheetApp.getUi();

  // create variables
  var activeCell = ss.getActiveCell()
  var formula = activeCell.getFormula()
  var cellref = formula.substring(1)
  var sheet = cellref.slice(0,cellref.indexOf('!'));


  var resp = ui.alert('Jump to '+cellref,'Are you sure you want to continue?',ui.ButtonSet.YES_NO);
    // Process the user's response.
    if (resp == ui.Button.YES) {
      // User clicked "Yes".
      // create sheet variable
      var targetsheet = ss.getSheetByName(sheet)
      var range = ss.getRange(formula.substring(1))
      targetsheet.setActiveSelection(range);
  } else {
      // User clicked "No" or X in the title bar.
      ui.alert('Jump cancelled.');
  }

}

Logic

  • Ui is used to confirm that the user wants to jump

  • the formula in the active cell is obtained

  • = is stripped off using .substring(1)

  • the sheet is obtained using cellref.indexOf('!')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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