SOLVED

Date to remain in field

Copper Contributor

Hello,

 

I have a column in my spreadsheet with the following formula:

 

=IF(A2="Overdue",TODAY(),"")

 

What I am looking to add to this is if the field in 'A2' changes, the date that was displaying in field H2 is to remain.

 

Currently it disappears if another option is chosen from the drop down in A2.

 

Is it possible to achieve what i am looking for and how would i do this?  

 

Any help would be greatly appreciated.

2 Replies
best response confirmed by Neill156 (Copper Contributor)
Solution

@Neill156 

Instead of a formula, use a bit of VBA:

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm)

Make sure that you allow macros when you open it.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cel As Range
    Set rng = Intersect(Range("A2:A100"), Target) ' Change range as needed
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each cel In rng
            If cel.Value = "Overdue" And cel.Offset(0, 7).Value = "" Then
                cel.Offset(0, 7).Value = Date
            End If
        Next cel
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
brilliant, thanks so much for this.
1 best response

Accepted Solutions
best response confirmed by Neill156 (Copper Contributor)
Solution

@Neill156 

Instead of a formula, use a bit of VBA:

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm)

Make sure that you allow macros when you open it.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cel As Range
    Set rng = Intersect(Range("A2:A100"), Target) ' Change range as needed
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each cel In rng
            If cel.Value = "Overdue" And cel.Offset(0, 7).Value = "" Then
                cel.Offset(0, 7).Value = Date
            End If
        Next cel
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

View solution in original post