2

In my Google Sheets spreadsheet, I have a named data range called "scores". I would like for each cell in this data range to be assigned a default formula, which takes data from another sheet. However, if the user wants to type in manual data, they should be able to. If the cell becomes blank, it should reinstate the default formula.

I looked around, and all I could find are people saying to use two cells.

This will not work for my situation, so what is the simplest way to accomplish this?

migrated from superuser.com Sep 10 '17 at 17:17

This question came from our site for computer enthusiasts and power users.

  • I guess you could do that with a script and a second sheet which can be hidden. – SpiderPig Sep 10 '17 at 16:50
  • I once made something like that, which I can try to dig up. It was something in line of @SpiderPig's suggestion. You have to decide, though, when the cell should be filled with the default value - only on edit, or when adding new rows/columns? Also, I hope you mean "it should reinstate the default value" - as having a formula copied from one sheet to another will be a bit painful. – Vidar S. Ramdal Sep 11 '17 at 7:57
  • @VidarS.Ramdal Thanks for the suggestions. I was able to create a script to do what I needed. Here is the code if you are interested: pastebin.com/eEGBRQEL. – vkumar Sep 14 '17 at 1:54
  • 1
    @vkumar: Please add your solution as an answer. That way, future readers with the same problem will see there's a possible solution. – ale Sep 20 '17 at 12:36
2

I was able to create a script to do what I needed.

I used the onEdit function provided by Google Sheets, so the script runs whenever a cell is edited.

In order to make the script run as efficiently as possible, I check first if the cell is in a sheet in which the cells should be replaced with the default formula if empty. If it is not, the script exits. Then, I check if it is in the right range, and finally if it is not blank. If it meets all the criteria, the cell's value is replaced with the default formula. Here is the script:

function onEdit(e) {

  var editRange = { // B3:AL13
    top : 3,
    bottom : 12,
    left : 2,
    right : 38
  };

  //Get sheet from which the event occured
  var range = e.range
  var sheet = range.getSheet();  
  var name = sheet.getName();

  //Exit if sheet is not a sheet where cells should be changed
  if (name != "Section 1 Homework") return; 

  // Exit if we're out of range
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;

  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;

  //Exit if the cell is not blank
  if (!range.isBlank()) return;

  //Set the cell value to the default formula
  range.setValue( "=if(INDEX('Section 1 Attendance'!A1:AL12, " + thisRow + ", " + thisCol + ") = \"A\", \"A\", \"\")" )
}

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.