Search code examples
excelif-statementexcel-formulacell

Excel =IF formula using Today() as a date stamp, populating response in unwanted cells

In cell AA2 I am using the formula =IF(A2:Z2<>A2:Z2, "", TODAY())

This formula is supposed to update AA2 with today's date if any edits are made to cells A2 through Z2. This works, but, it also populates the next 25 cells in the row with the equation. The formula is not a dark black color in the top box as it normally would be but instead a subdued gray. Only when the formula is activated does this occur, so if the row has no entries there is no date stamp or extra cells with a date stamp.

Is there a way to limit this formula to only being used and displayed in cell AA2?

Excel snippet

Solution

  • I managed to solve this problem. It required use of a VBA macro which also requires the excel document to be saved as .xlsm

    First in the VBA editor I created a new module with the following code:

    Sub UpdateTimeStamp()
Dim Target As Range
Dim targetRow As Long
Set Target = Range("A:Z")  ' Replace A:Z with your actual data range  
Set Target = Application.Intersect(Target, ActiveCell)
If Target Is Nothing Then Exit Sub
targetRow = Target.Row
Worksheets("Data-Set").Cells(targetRow, 27).Offset(-1, 0).Value =  
Now 
End Sub

    Next under the worksheet itself in the VBA editor I input the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:Z")) Is Nothing Then 
UpdateTimeStamp 
    End If
End Sub