If condition, copy a cell value

I have a cell (A1) that will be changed over time. If a certain condition is met I want the current value of A1 to be copied to for example B1. But I don’t want B1 to change when A1 is changed again.

So for example:
=IF(A1 > 5, value of A1, “-”)

I have a few cells with different conditions so the value of A1 will be saved at multiple instances.

Hope this is clear enough.

You can’t copy cell content using formulas. You can reference content of other cells using formulas (i.e. here: IF() formula/function). Remember: Adding =IF(A1 > 5;A1; "-") into cell B1 means: The content of cell B1 is a formula. Now you want that formula to replace itself by a value. That’s not possible. The only thing you could do here: Once there is a value in B1 use: Data -> Calculate -> Formula to Value (This is exactly what you expect a formula to do on it own - pretty self-destructive :slight_smile: ).

You want to keep something what I call a “history” (i.e. a remembrance in B1 what A1 contained some time in the past) and that’s impossible without macros or manual editing based on date, numbering or something similar - or just a completely different design of your sheet. Since you did not clearly describe why you do not want B1 to be changed on consecutive changes of A1, there is little chance for a proposal of a better design.

If you do with formula, it will be changed when it meets the condition.