Locking a cell value after successful IF function.

Occasional Contributor

Hello, i have a result (number) in a cell as a result of a successful IF function, which is controlled on the sheet under an auto-refresh. I want to 'lock' or leave, the value in the cell after the IF function places it there, and particularly, after the sheet auto-refreshes and the IF function turns the value in question 'off'. Is there a way to do this? Or, is there another function i can use which would help? TIA

13 Replies


That probably requires VBA code, but we'd need to have more detailed information.

  • What is the formula?
  • Which cell or cells have the formula?
=IFS(K4=TRUE, $J$5)
Formula lies in K5, L5, M5 etc


Thank you! Will the user enter TRUE or FALSE in K4, L4 etc., or do those cells contain formulas too?

I'm trying to find out which cells, when edited directly by the user, will determine the result of the formulas in K5, L5 etc.

@Hans Vogelaar 


Thus is the code that generates the true/false, its looking at 2 numbers only, factoring in a tolerance when matching their similarity.


And are G2 and K2 entered by the user, or do they contain formulas too?

They are mirroring another cell, but that's it, just a number. It's a decimal number


What are the formulas in G2 and K2?

In G2 ::: =G1
In K2 ::: =K1

Both numbers they source are decimal versions of date/time


Thanks. Are G1 and K1 entered by the user, or do they contain formulas too?

(I hope that you see where this is going)

I have no idea actually haha, am i completely off track?? One is equal to time =NOW() for G2

K2 is a certain time of day in date/hours/minutes format


We're not getting any closer, and I fear it's too complicated, sorry.

Wow. Ummm I'm close enough.... just needed help with the final step. Too complicated for someone (I'm assuming, yourself helping people on this forum...) who is experienced with excel? Have you got any mates who might help?