-1

Is there a way to way to link two cells so when one is altered the other is altered also?

Like this:

I alter `A1` to 1, `B1` is now 1
I alter `B1` to -1, `A1` is now -1
  • @pnuts The cells will be at different sheets, they have the same meaningful value (meaning both cells have to have the same value at all times). The thing I'm trying here is to avoid creating a "config/shared info" sheet. – Nicos Karalis Apr 7 '17 at 12:36
  • 1
    You might be able to do something with a script and onEdit(), but I'm not GAS expert. – ale Apr 7 '17 at 14:24
3

You can link two cells using a google script. You can use on Edit trigger of each sheet to poll for which cell is getting modified and modify the other one in the other sheet.

The below code will link cell A1 in "Sheet1" in of Spreadsheet with ID spd1ID to B1 in "Sheet1" in Spreadsheet with ID spd2ID

var spd1ID = "ID of first sheet here"
var spd2ID = "ID of second sheet here"

function myLinkCells() {
 ScriptApp.newTrigger("callOnEdit").forSpreadsheet(spd1ID).onEdit().create()
 ScriptApp.newTrigger("callOnEdit").forSpreadsheet(spd2ID).onEdit().create()
}


function callOnEdit(e){
  var range = e.range
  var cellAdd = range.getA1Notation()
  Logger.log("Called")
  var ssID = e.source.getId()
  var sName = range.getSheet().getSheetName()
  if( ssID == spd1ID && sName == "Sheet1"){
  if(cellAdd == "A1"){
     var ss = SpreadsheetApp.openById(spd2ID)
     var rng = ss.getSheetByName("Sheet1").getRange("B1")
     rng.getSheet().getSheetName()
     rng.setValue(e.value)
     Logger.log("In Cell A1")
     }
  } else if(ssID == spd2ID && sName == "Sheet1"){
    if(cellAdd == "B1"){
     var ss = SpreadsheetApp.openById(spd1ID)
     var rng = ss.getSheetByName("Sheet1").getRange("A1")
     rng.setValue(e.value)
     Logger.log("In Cell B1")

     }
  }
}

Where to paste this code? Open your spreadsheet Tools>Script Editor

What is spreadsheet ID? https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id

After you paste the code and modify the ID, run myLinkCells() function using Run menu. That will link the cells.

Hope that helps!

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.