0

I know about the importRange function to link two sheets, but I wonder if there's a way so that when a cell's contents are imported to the second sheet, the second sheet will autopopulate the corresponding cell with simply an x and nothing else. So for example, if Sheet1!B2 has a value of “23”, then Sheet2!B2 will appear as x.

(I'm not familiar with JavaScript or Google Apps Script so any coding examples would be highly appreciated.)

1

From inside the Google spreadsheet, go to Tools -> Script editor then try this:

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 1) { //checks the column
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var XSheet = ss.getSheetByName("Sheet2");
     ss.setActiveSheet(XSheet);
     SpreadsheetApp.getActiveSheet().getRange(r.getRow(), r.getColumn()).setValue("X");
     var MainSheet = ss.getSheetByName("Sheet1");
     ss.setActiveSheet(MainSheet);   };
 };
}

This assumes your sheets are named Sheet1 and Sheet2, as is the default, and it's set to only watch column E (5) on Sheet1 for edits and only add an "X" to the corresponding cell in Sheet2.

1

For a simpler solution you can use arrayformula:

Just put the following into Sheet2!A1 and watch the magic.

=ARRAYFORMULA(if(isblank(Sheet1!A:Z),"","x"))

You can change the range Sheet1!A:Z to a specific range if you need, and you can put the formula into whichever cell you want the x's to start from.

  • 1
    Yes they are - will switch them – Tom Horwood Dec 4 '17 at 23:12

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.