Copy value from formula result on event change of cell value
 

Hi,

I need to do something very tricky. I have a workbook where we track
the progress of a project by putting "x" in a cell when a milestone is
passed. What I would like to do is to save the date (of Today()) when
the "x" is entered in the cell. Pseudo code would be something like:
On event (cellvalue changes to x) -- catch todays date and store it
as hard coded value in another cell.

The thing I really need help with is to store the value from the
"Today()" formula as a hard coded value based on the change event of
entering x in the cell.

Tricky one - hope someone can help me with this!

Thanks!

Copy value from formula result on event change of cell value
 

Right click sheet tabcopy/paste thischange c4 and c5 to suit. Now when you
change the first cell the second will show today's date.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("c4")) Is Nothing Then Exit Sub
If UCase(Target) = "X" Then Range("c5") = Date
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
Hi,

I need to do something very tricky. I have a workbook where we track
the progress of a project by putting "x" in a cell when a milestone is
passed. What I would like to do is to save the date (of Today()) when
the "x" is entered in the cell. Pseudo code would be something like:
On event (cellvalue changes to x) -- catch todays date and store it
as hard coded value in another cell.

The thing I really need help with is to store the value from the
"Today()" formula as a hard coded value based on the change event of
entering x in the cell.

Tricky one - hope someone can help me with this!

Thanks!

Copy value from formula result on event change of cell value
 

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.
As written, the code will store the date in column D when an x is entered into column A.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
If Target.Value = "x" Then
Application.EnableEvents = False
Cells(Target.Row, "D").Value = Date
Application.EnableEvents = True
End If
End Sub


wrote in message
...
Hi,

I need to do something very tricky. I have a workbook where we track
the progress of a project by putting "x" in a cell when a milestone is
passed. What I would like to do is to save the date (of Today()) when
the "x" is entered in the cell. Pseudo code would be something like:
On event (cellvalue changes to x) -- catch todays date and store it
as hard coded value in another cell.

The thing I really need help with is to store the value from the
"Today()" formula as a hard coded value based on the change event of
entering x in the cell.

Tricky one - hope someone can help me with this!

Thanks!

Copy value from formula result on event change of cell value
 

On 23 Maj, 16:00, "Don Guillett" wrote:
Right click sheet tabcopy/paste thischange c4 and c5 to suit. Now when you
change the first cell the second will show today's date.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("c4")) Is Nothing Then Exit Sub
If UCase(Target) = "X" Then Range("c5") = Date
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...



Hi,

I need to do something very tricky. I have a workbook where we track
the progress of a project by putting "x" in a cell when a milestone is
passed. What I would like to do is to save the date (of Today()) when
the "x" is entered in the cell. Pseudo code would be something like:
On event (cellvalue changes to x) -- catch todays date and store it
as hard coded value in another cell.

The thing I really need help with is to store the value from the
"Today()" formula as a hard coded value based on the change event of
entering x in the cell.

Tricky one - hope someone can help me with this!

Thanks!- Dölj citerad text -

- Visa citerad text -

Splendid!

Both alternatives work as a charm!

Mille Grazi!