Need to change existing formula into values...

[Sriram]

Dear Friends,

I'm using a formula in excel and want to convert the formula into values, so
that it won't change further. Let me explain it.

The thing is that, I want to enter the current date if the user enters "Yes"
into it previous column. i.e.,

Example:

A B - G H ...
1
2
3...

The Column H will be Locked by me, so the user can't even select that cell.
There I've a formulae as below.

=IF(G2="Yes",TODAY(),"")

Column G will be having 2 set of values which can be entered by the user by
means of List control. The values are "Yes" & "No".

If the user selects "Yes" in Column G, then the Current Date will be entered
automatically with the above formulae in the respective row's Column H.

My question is, if there user selects as in example above second row
G-Column as Yes, then 2nd row Column H will be entered automatically as
today's date "19-May-06". If he opens the file tomorrow, then 2nd row
Column-H value will be changed as "20-May-06". This should not happen.

Once the date has been entered, then it should FREEZE with the entered date
and should not change further onwards.

Kindly resolve my problem.

Thanks in Advance...

Sriram Subramaniyan

 

[Don Guillett]

right click sheet tab>view code>insert this>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 7 Then Exit Sub
If UCase(Target) = "YES" Then Target.Offset(, 1) = Date
End Sub