if statement true=now false retains last true

1

173

Joined
Aug 31, 2008
Messages
2
Reaction score
0
What I have is a worksheet that automatically updates info into it every minute. I have a cell that evaluates if the inported data is true or false. I want another cell to enter the current time when the true statement is activated. This cell should retain the previous true statements value until a new true statment is made. For example:

At the listed times, A1 & B1 should read as follows:
11:02 A1=FALSE B1=11:01
11:01 A1=TRUE B1=11:01
11:00 A1=TRUE B1=11:00
10:59 A1=FALSE B1=10:56
10:58 A1=FALSE B1=10:56
10:57 A1=FALSE B1=10:56
10:56 A1=TRUE B1=10:56

Can an If statement be used?
Something like: =IF(A1=TRUE,now(),)
Any help out there?
Thanks
 
The only way that I can think of requires VB, you could put this in the ThisWorkbook code sheet
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    If CBool(CStr(ThisWorkbook.Sheets("sheet1").Range("A1"))) Then
        ThisWorkbook.Names.Add Name:="LastTrue", RefersToR1C1:=Now()
    End If
End Sub
and in B1, put the formula
=LastTrue
 
Back
Top