Forum Discussion
Keep a Value
Something like that, in that direction?
If cell A2 contains the text "x"; then enter the value 9 in cell A1; otherwise "leave everything as it is"
VBA code:Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$ A $ 2" Then If LCase (Range ("A2")) = "x" Then Application.EnableEvents = False Range ("A1") = 9 Application.EnableEvents = True End If End If End Subor in formula?
=IF(A2="x",9,A1)
Hope I was able to help you.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here
kobus1305 a cell can only hold a Value OR a Formula. If you put that formula in the cell and the use types a date in that cell it will overwrite/erase the formula in that cell. You need to create a helper column so for example column D is Entered Date and column E has the formula.
Hi,
Thank you this is what i understood. =IF(AND($A$1<>0,A4<>0,B4<>0),D4=TODAY(),"") this formula is in E4 and i want the today() in D4 only if conditions is correct. Is that what you meant? The D4=Today() do not enter the today() in D4???
Thank You
Regards
kobus1305 no. But as I write out this answer it occurs to me that you probably looking for a timestamp, a common request (I'll address below). (Initially) It sounds like someone is entering dates in D4 and you only want to use that date if certain conditions are met, otherwise use TODAY(). so in E4 you apply the formula =IF(AND($A$1<>0,A4<>0,B4<>0),TODAY(),D4) and then use E4 for the calculation you want.
That said (as I noted above) I'm now thinking you want a timestamp to be applied when certain conditions get met. This is not easy in Excel but there are 2 ways I've seen it done.
1) you can create a circular reference and then in the setting tell Excel to compute circular references 1x so something like in cell D4 the formula =IF(AND($A$1<>0,A4<>0,B4<>0, D4=0),D4=TODAY(),D4).
2) you can use a macro something like:
Private Sub Worksheet_Change (ByVal Target As Range) IF (Range("$A$1")<>0 AND Range("A4")<>0 AND Range("B4")<>0 AND Range("D4")="") Then Range("D4").value = Date() End If End SubIf you need a date stamp in more than D4 (i.e. the whole column D based on the whole columns of A and B then you need to create a loop. but more efficiently check the Target cell and process only if that was something that would be applicable (e.g. column is A or B) and then process that row.
But since you weren't clear about your needs/intentions it is hard to guess what you need.
