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.
Hi Thanks a million. Know this circular reference gives me a headache i know what it means but sometimes it takes it to far. I have a column "M", in the header i keep track, IFNA(SUM(MATCH("Success",M4:M160,0)+3),0), of which document, in that column 160 possible documents, was successful after selecting a drop down list, "Success", "Failure", "Archived". You select one as "Success" and the header change to that row number no problem. When the validity date of a document passes todays date it automatically changes to "Failure". After checking that all info on another spreadsheet, Successful Documents.xlsm, is completed you select "Archived" which works but Circular Reference Error display comes up. Know how can i disable or handle that so it does not happen???
Thank You
Regards
Kobus
kobus1305 sorry but you lost me on what you are doing. maybe a sample sheet (or 2) would help. that said you final question about the circular reference error I can speak to; as I mentioned in the above comment you need to change an Excel setting for iterative calculations:
File -> Options -> Formulas -> check the box for Enable iterative calculation and set maximum iterations to 1 (you could do higher but why waste processing time) and maximum change to something larger than the number you expect: e.g. 999999.
I never use this circular reference technique so if anyone else has tips or recommendations that would be helpful.