0

E.g. If the range of cells is A1:A20 and the independent variable is A5. If A5 is modified then the content of A1 to A20 except for A5 will be cleared.

4
  • 1
    Have you googled anything. Dec 27, 2022 at 7:01
  • Hi and welcome. Is there a function A built-in function will not clear the contents of a cell/range. The answer by @doubleunary is a script, and that's your only option to physically delete the contents of cell. If that's not what you wanted/expected, then you need to provide more information in your question.
    – Tedinoz
    Dec 28, 2022 at 1:21
  • As the asker, you have a special privilege when a good answer is given to your question. You can accept the answer that you believe is the best solution to your problem. "Accepting" an answer is a way of acknowledging the work of the volunteer as well as helping others in the future who may have a similar question to yours.
    – Tedinoz
    Dec 28, 2022 at 1:26

1 Answer 1

1

If the value in A5 is the only value in the column A1:A20 that is manually entered, and the rest of the values are calculated with formulas, wrap those formulas in if(), like this:

=if(isblank(A5), iferror(1/0), "another value based on " & A5 & " and perhaps some other references")

If the values in A1:A20 are manually entered, you will need a script to clear them. Use an onEdit(e) simple trigger, like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  let sheet;
  if (!e.value
    || e.range.rowStart !== 5
    || e.range.columnStart !== 1 // column A
    || (sheet = e.range.getSheet()).getName().match(/^(Sheet1|Sheet2|Sheet3)$/i)) {
    return;
  }
  sheet.getRangeList(['A1:A4', 'A6:A10']).clearContent();
}

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.