Need to stop formula and keep value on met condition
 

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.

Need to stop formula and keep value on met condition
 

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" wrote:

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.

Need to stop formula and keep value on met condition
 

Works great Mike H. Thank you.

"Mike H" wrote:

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" wrote:

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.