0

I am looking for a script that will clear cell(s) when the value of another cell changes or is equal to ("X"). capture1

In the attachment I would like when Column "G" gets checked marked (TRUE), then Columns "D" & "E" become cleared (or no value) according to the respective row that was checked (TRUE).

For Example:

When "G3" is checked (TRUE), then D3 & E3 are cleared. As you can see that it is important that both columns get cleared out as it affects Columns "E", "F", and "H" which all include very important information that needs to be automatically updated according to Column "G" to remove as much human error as possible. Capture2

I have done some research on my own searching through this site, other sites and forums, and google support, etc. There was one that was close to this that I wanted, but it would "hide" the row, which I don't want and supposedly it would clear a cell but I couldn't get that to work. i attempted to make my own edits but I do not have experience enough to troubleshoot.

Here is something that I found and couldn't troubleshoot:

function onEdit(e) {
    var sh = e.source.getActiveSheet();
    if (sh.getName() !== 'Sheet2' || e.range.columnStart !== 3 || e.range.rowStart < 2 || e.value !== 'No') return;
    e.range.offset(0, 3).clearContent()
    sh.hideRows(e.range.rowStart)
}

Then change the highlighted text to the name of the sheet/tab you want the script to work on. Finally close the script editor (!) and navigate to that sheet. Try entering 'No' in col C and see what happens.

I edited it to be:

function onEdit(e) {
    var sh = e.source.getActiveSheet();
    if (sh.getName() !== 'Main' || e.range.columnStart !== 7 || e.range.rowStart < 2 || e.value !== 'FALSE') return;
    e.range.offset(0, 3).clearContent()
    sh.hideRows(e.range.rowStart)
}

I can get it to hide the row (which I don't want), but I can't get it to clear a cell, let alone two of them.

Here is what I have from Raymond Tran's answer (good workaround for now):

Before

After

  • This is a duplicate of How to Clear a Cell based on the Value of another Cell. If you want to improve your question it's better to edit the original post instead of posting a new one. – Rubén Dec 27 '19 at 15:57
  • Thank you, I do not have access to edit that previous question and so that is why I duplicated to be able to edit as necessary and have more "control" over when there are comments or answers. – DreamLvg Dec 27 '19 at 16:01
  • It looks that you have more than one account. Please follow the instructions of I accidentally created two accounts; how do I merge them? – Rubén Dec 27 '19 at 16:05
  • In the mean time, please read developers.google.com/apps-script/guides/sheets and add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén Dec 27 '19 at 16:07
  • Thank you for the help Rubén, put in a request with the site. Yes, I have done some research on my own searching through this site, other sites and forums, and google support, etc. There was one that was close to this that I wanted, but it would "hide" the row, which I don't want and supposedly it would clear a cell but I couldn't get that to work. i attempted to make my own edits but I do not have experience enough to troubleshoot. – DreamLvg Dec 27 '19 at 16:16
0

The code line that hides the row is

sh.hideRows(e.range.rowStart)

To avoid that the row be hidden, just remove that line.

The code line that clears a a cell content is

e.range.offset(0, 3).clearContent()

It's clearing a cell in the same row, three columns to the right, i.e. if G3 is edited then the content of J3 is cleared. To clear the content of C3 and D3 change the above code line by

e.range.offset(0, -4, 1, 2).clearContent()

Reference

| improve this answer | |
  • Thank you Rubén, I still don't seem to be able to get it to "clear" the cell of value. – DreamLvg Dec 27 '19 at 18:14
  • I edited my answer. – Rubén Dec 27 '19 at 18:25
  • Awesome, it worked,,,, can you explain how the C3 and D3 were cleared? I would want D3 and E3 cleared. – DreamLvg Dec 27 '19 at 19:55
  • @DreamLvg I added a reference. – Rubén Dec 27 '19 at 20:45
  • Awesome. Now should I ask this in another question?: Now I Froze the top two rows (the titles and the "main" totals [put the totals above the titles so that when I apply the filter to the titles they will still always be showing]). The Question is: Is there a way to (dependent on the filter applied), that those "main" totals auto adjust to the filter? Or "too" complicated? So if someone sorts by vendor, the column of total past due and the total a/p adjust to that sorted list. and when it is unsorted it goes back to calculating the whole list. – DreamLvg Dec 27 '19 at 23:29
0

Let me know what you think about this solution. Column D and E can be grouped. Add two extra columns with the formula in my screenshot. Work of the two new columns and update other formulas to reference your new columns.

Formula: =IF(C2=true,value("$0"),A2)

enter image description here

| improve this answer | |
  • This is a great workaround, and I understand the sheet. Hopefully, there is a way to do this without adding extra columns to avoid confusion when someone looks at this. So I titled columns as the false total and false aging and then the ones with your equation as True total and true aging. Thank you for this. The Ctrl+G doesn't seem to be working for me so I can't show you the images of your answer but I will add it to the question of before and afters. – DreamLvg Dec 27 '19 at 18:06

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.