Keep a Value

[kobus1305]

I may be this is a stupid question but i am sitting and thinking is it all possible. We all know how if statement works. Logical test, if test is true, if test is false!! Is it possible that, if test is false, keep the value that was in the cell were the if statement is used?? 

[#2 · Accepted solution]

@kobus1305 

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 Sub

 or 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

[#3]

Hi, For more clearity this is what i am trying to do. =IF(and($A$1<>0,A4<>0,B4<>0),TODAY(),if(and($A$1=0,A4<>0,B4<>0),Know I do not want to change the date to today, it may be a later date, this Cell is D4 and it must keep the date that was in there. Thanks for your patience with me!!!!! Thank You Regards

[#4]

@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.

[#5]

@mtarler 

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

[#6]

@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 Sub

If 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.

[#7]

@mtarler 

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

[#8]

@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.

[#9]

@mtarler 

Hi, Thanks so so so much that circular reference effort is gone!!!!!!!!!!!!!!!!!!!!!!!!!!

You are an angel!!!!

Regards

Thank You