Clear cell content based on calculated value in another cell

Hi,

I have the following problem:
I have formulas in cells K21, K22, K23 and text in cells L21, L22, L23. I would like that whenever the value changes (based on the respective calculation) in either K21, K22 or K23 that the text in L21:L23 in cleared.
Does anyone have an idea?

Thanks a lot,

Paul

There is no event to recognize a change in a formula value. We would need to know which cell the formula references then set the macro to trigger based upon a change in that cell. We could base it upon any change in any cell but I try not to do that unless absolutely neccessary.

I'm with stnkynts, and I'm interested to see a solution to this problem. I was thinking something like this at first:

Please Login or Register  to view this content.

So if a formula in K21 is =SUM($B$2:$B$4) and I change the value in B2 or B4, this code works great. But obviously the If Statement will never be true if I change B3, even though the value in K21 would change.

So I guess I would say you could use this if none of your formulas use ranges, but otherwise this isn't the correct solution.

thanks for the feedback. Unfortunately the formulas in K21 etc. are dependent on input from other cells that also contain formulas (it is actually a rather extensive profitability calculation and K21 gives the result). Any other thoughts?

You could always convert the entire thing to VBA, i.e. remove all the formulas from the spreadsheet and do all the calculations in VBA.

Eventually the formulas go back to a single cell. I would set the change event based upon that cell.

I'm still not sure that would resolve the issue I mentioned above where if the changed cell is in the middle of a range listed in the formula.

Thanks. Unfortunately, it does not go back to a single cell but to a series of values. The values in K21, 22, 23 only change if the profitability (calculated from a series of values) changes to specified thresholds. So I guess that this cannot be done. Thanks anyway for your fast replies.

I still think it's likely that all of that could be done in VBA, but ok.

Thanks. Unfortunately, I cannot share the file as it is confidential. However, as already said the values in K21, K22, K23 depend on a multitude of cells some of which also are formulas, i.e. there is not a single value that it all goes back to. I will try to clear the file of all confidential info and share with you.
Thanks for the support. I appreciate.