ClearContent of cell when another cell on the row changes via formula

[jimmisavage]

Hi all,
I want to clear cells E and H when cell B (on the same row) changes via formula.

I have a formula that will change if I physically change the cell but it won't work when it changes via a formula. Can anyone help with this?

Please Login or Register  to view this content.

[Logit]

.
This seems to work well :

Please Login or Register  to view this content.

[jimmisavage]

Hi Logit,
That works perfectly for my needs. Thanks for the help!

[Logit]

You are welcome.

[jimmisavage]

Hi Logit,
I've just noticed something a bit odd which I wasn't expecting. It seems to delete the entire columns when I make a change when I only really want the data on the same row that was changed to be deleted.

Please Login or Register  to view this content.

So when cell C605 is changed I only want cells E605, H605, K605, N605 and Z605 but it's deleting everything in E,H,K,N & Z

How would I fix this.

EDIT:
On closer inspection I think something else is causing this bug. Something is causing the chosen columns to delete when entering a new line. I'll upload an example now so you can see what I mean.

[jimmisavage]

The word document shows what I mean and I've also attached a test.xlsm so you can see it happen yourself.

[jimmisavage]

If Logit isn't available can anyone else suggest any edits here? I've tried everything I can think of but with no luck

[Logit]

.
jimmisavage

I can't reproduce the error here on my example workbook attached above.

I downloaded your file and verified what you are experiencing there ... it deletes everything in the column.

The only difference I can visualize between your workbook and mine involves your formulas referencing data on another sheet. I changed my example workbook to pickup on
a cell data change from another sheet .... it still functions as it should (on my workbook).


The only thing I can imagine (although there may be some other reason ...) is something has become corrupted in your workbook. What that is would be difficult to determine.


I tried running a small "error cleaner process" on your workbook but the process doesn't recognize that your workbook even exists. That has never occurred before with that small cleaner
program. In the past, if there were no errors to correct it would state such - it has never said (basically) 'I can't find that workbook.' Very odd.

Consider creating a new workbook from scratch. Do not copy and paste anything from your existing workbooks because you'll most likely copy what ever is causing the error. See if that
helps. Just create something with 10 or 20 rows to test. I hope that will clear up the issue.

[jimmisavage]

Hi Logit,
I seem to have fixed the problem!
I tried starting from scratch but still didn't get the required outcome - it was still deleting everything for whatever reason.

When trying to work out how best to use the reference cell i decided to add a vba code to populate that cell when it was needed. This simple change fixed the issue! Before I was referencing another cell to get the reference but not it's inputted directly it's working like a charm.

Thanks again for the help. I appreciate the time you guys put in

[Logit]

.
Glad you found a fix for the issue.

You are welcome.

Cheers.