3

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?

  • 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\", \"\")" )
}
| improve this answer | |
1

I have a more kludgy way to solve this problem.

What I do is to set the value of all of the cells in the column to this function: =IF(ISBLANK(A2), “”, “Default Value”)

This makes the cell appear blank until that line is edited. Then the value becomes the default. But then as the user is entering data, they can just overwrite the default value with the correct value without any effort.

| improve this answer | |

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.