Printable View
The aim is to place a date in a cell, which is dependent on antoher cell showing the value- 100% (manuallly) and then to fix that date permanently.
The problem is that this date keeps on updating when the spread sheet is opened on a new day.
I'm using this formula and function
=IF(G81>99%,fixeddate(),"")
Option Explicit
Function FixedDate()
FixedDate = Date
End Function
Any help will be appreciated
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$81" Then
If Target.Value > 0.99 Then
Me.Range("H81").Value = Date
End If
End If
End Sub[/VBA]
Thanks, this formula has to be copied down to several cells, can I change the target.adress to a range e.g. G81:G134?
Or does a range require something else?
good article on exactly this kind of thing: http://chandoo.org/wp/2009/01/08/tim...-formula-help/
resolution would be:
1- turn circular references on
2- change formula fixeddate to:
[VBA]Function fixeddate() As Long
fixeddate = Now
End Function[/VBA]
3- put in cell formula of =IF(G81>0.99, IF(H81="",fixeddate(),H81),"") assuming cell H81 is dependant on G81.
Note: This will change the cell to "" if you lower the number below .99 If you want that part removed, you will need to remove the final ""
Thanks this will help!
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("G81:G134")) Is Nothing Then
If Target.Value > 0.99 Then
Target.Offset(0, 1).Value = Date
End If
End If
End Sub
[/VBA]
XLD,
Is there a performance difference between the circular reference solution and your worksheet change solution, and what would that performance difference be?
Perhaps this UDF will work for you.
[VBA]Function LockDate(Refresh As Boolean) As Date
If Refresh Then
LockDate = Date
Else
LockDate = Application.Caller.Text
If IsDate(LockDate) Then
LockDate = DateValue(LockDate)
ElseIf IsNumeric(LockDate) Then
LockDate = Val(LockDate)
End If
End If
End Function[/VBA]
If B1 holds the formula =LockDate(A1=1),
changing A1 to 1 will put the current date as the value of that cell.
change A1 to 2 and the value will not change. Day after day..it will not change, until the user puts 1 in A1, at which time the value will be that day.
Any difference will be negligible and not noticeable, but allowing circular references is not a good idea in my view, it might hide a problem.
thanks everyone, these are great solutions
I agree with XLD, I'm not a fan of circular references