Don´t want to update now().

[Ronix]

Hi Guys,

in cell B1 is this statement =IF(E1="OK";NOW();"").
The range for now() values will be in B1:B1000 and OK/NOK values will be in E1:E1000.
But I need to have now() as a fixed value (non updating everytime I open the file).
Thx in advance!

 

[jtakw]

Re: Don´t want to updae now().

Hi,

Fairly sure you'll need VBA to do that.

 

[Jonmo1]

Re: Don´t want to updae now().

This is one case where you can use circular references.
But be careful, I'm not fully aware of all possible consequences of doing this..

So in File - Options - Formulas
Check Enable Iterations, and set the number pretty low, like 5 or something.

First set E1 to NOT OK (so the IF returns as false)
Then use
=IF(E1="OK",IF(ISNUMBER(B1),B1,NOW()),"")

B1 is where you put the formula
E1 is where you put OK

 

[Ronix]

Re: Don´t want to updae now().

This is one case where you can use circular references.
But be careful, I'm not fully aware of all possible consequences of doing this..

So in File - Options - Formulas
Check Enable Iterations, and set the number pretty low, like 5 or something.

First set E1 to NOT OK (so the IF returns as false)
Then use
=IF(E1="OK",IF(ISNUMBER(B1),B1,NOW()),"")

B1 is where you put the formula
E1 is where you put OK

Thx for help. But this file will open a lot of people. Vba should do that, I´ve found this script but dont know how to modify it to my task.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 2 Then
If Target.Value = "" Then
Cells(2, 2).Value = ""
Else
Cells(2, 2).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
End If
End If
End Sub</code>

 

[Ronix]

Re: Don´t want to updae now().

Hi Guys,

any idea how to get this works?
I am not familiar with macros.
Thx!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E1:E1000")).Value = "NOK" Then
If Intersect(Target, Range("B1:B1000")).Value = ""
Else
Intersect(ActiveCell, Range("B1:B1000")).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
End If
End If
End Sub

 

[Jonmo1]

Re: Don´t want to updae now().

Try this

Right click the sheet's tab, click View Code
Paste the following

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, c As Range
Set MyRange = Intersect(Target, Range("E1:E1000"))
Application.EnableEvents = False
On Error GoTo handlr
If Not MyRange Is Nothing Then
    For Each c In MyRange
        If c.Value = "OK" Then
            If IsEmpty(Range("B" & c.Row)) Then Range("B" & c.Row).Value = Now
        Else
            Range("B" & c.Row).ClearContents
        End If
    Next c
End If

handlr:
Application.EnableEvents = True
End Sub

 

[Ronix]

Re: Don´t want to updae now().

Hi Jonmo1,

many thanks for your help. It works well.
Thx!

 

[Jonmo1]

Re: Don´t want to updae now().

You're welcome.