Mar 25 2022 03:53 AM
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.
Mar 25 2022 04:39 AM
SolutionInstead 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
Mar 25 2022 04:39 AM
SolutionInstead 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