Need to stop formula and keep value on met condition

[AUCP03]

I have a spreadsheet I use to track tasks for my branch. All the information
is in a list.
Column C is due dates [starting in row 3]
Cell J2 is the function =TODAY() [outside of the list]
Column E is days left until due via the formula =-(J$2-C#) [starting in row
3]
Column D is either "Open" or "Closed" depending if the task is complete or
not. This column is manually changed and is always spelled exactly the same
thanks to predictive text.

What I would like is when Column D is changed from "Open" to "Closed" that
the value in column E stops changing.
This would allow me to see how early or late tasks are completed. Thank you.

 

[Mike H]

Hi,

You need a macro for that. This changes the formula in Column E to a value
when column C changes to Close. Right click your sheet tab, view code and
paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Or Target.Cells.Count > 1 Then Exit Sub
If UCase(Target.Value) = "CLOSED" Then
Target.Offset(, 1).Value = Target.Offset(, 1).Value
End If
End Sub

Mike

 

[AUCP03]

Works great Mike H. Thank you.

Hi,

You need a macro for that. This changes the formula in Column E to a value
when column C changes to Close. Right click your sheet tab, view code and
paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Or Target.Cells.Count > 1 Then Exit Sub
If UCase(Target.Value) = "CLOSED" Then
Target.Offset(, 1).Value = Target.Offset(, 1).Value
End If
End Sub

Mike

I have a spreadsheet I use to track tasks for my branch. All the information
is in a list.
Column C is due dates [starting in row 3]
Cell J2 is the function =TODAY() [outside of the list]
Column E is days left until due via the formula =-(J$2-C#) [starting in row
3]
Column D is either "Open" or "Closed" depending if the task is complete or
not. This column is manually changed and is always spelled exactly the same
thanks to predictive text.

What I would like is when Column D is changed from "Open" to "Closed" that
the value in column E stops changing.
This would allow me to see how early or late tasks are completed. Thank you.