Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condition formula | Excel Discussion (Misc queries) | |||
If formula with two condition | Excel Worksheet Functions | |||
conditional: stop function under certain condition | Excel Worksheet Functions | |||
MORE THAN ONE CONDITION IN A FORMULA | Excel Worksheet Functions | |||
Add condition to formula | Excel Worksheet Functions |