Timestamp in a single cell that updates automatically only when a cell value is CHANGED in a Range on one Sheet

[Excel_User_10k]

Hi All,

I have seen a few things on the internet regarding time stamping when a cell is changed. However, they are always about multiple times stamps for each and every Cell that is changed in one Column, with a corresponding timestamp alongside each cell in another column.

All I wish to do is have a timestamp that only updates in once cell, P3 for example, when a change is made in any of a Range of cells, F8:P439 for instance.

I am hoping to achieve this with a Formula rather than VBA too please. That way, if someone makes a change on a mobile device rather than a PC, the time will still update. It also avoids any errors or instability that almost always seems to follow the use of VBA.

I have also noticed it usually uses the loop error system. Which is fine, but it seems to always require another Column to be used to "copy" the value of the initial Column, and that update is how the timestamp is generated. As I have a rather large table. This would seem impractical and a waste of space. Is it a case of using a Formula and having to do that, or using VBA?

Any ideas please?

 

[6StringJazzer]

This requires a macro. A formula cannot detect when any cell within a given range changes.

 

[Dan_W]

I agree with @6StringJazzer - you would need VBA in order to accomplish this.

 

[Excel_User_10k]

Ok thanks guys. Would you happen to know what the VBA would be for that then please?

 

[6StringJazzer]

I assume F8:P439 has values. If they have formulas then you need a different solution. If they are formulas please provide information about what the formulas look like.

Here is the code that you would put in the module for the worksheet containing this data:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("F8:P439")) Is Nothing Then
      [P3] = Now
   End If
   
End Sub

 

[Excel_User_10k]

I assume F8:P439 has values. If they have formulas then you need a different solution. If they are formulas please provide information about what the formulas look like.

Here is the code that you would put in the module for the worksheet containing this data:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("F8:P439")) Is Nothing Then
      [P3] = Now
   End If
  
End Sub

So there are Formula's in that Range. However, it isn't the Formula's that I am wanting to track. I just thought that was easier to ask the question. The Formula's work based on the entries entered in cells around them. Would it be easier if I picked out all of the Ranges that these are? Or is it just easier to tweak the code to include the Cells that contain Formula's?

 

[6StringJazzer]

The Change event will not detect if a formula changes what result it returns. It only detects if a cell entry changes, like when someone types a value in. So you would have to monitor any changes to cells that a user could change that would in turn affect the cells you're interested in.

 

[Excel_User_10k]

Ok, well the formula's only change based on the entry of cells in that Range anyway. So that should be ok shouldn't it?

 

[6StringJazzer]

Yes.

 

[Excel_User_10k]

I already have a Worksheet_Change Macro in place to auto filter for me (as shown below), so it won't allow your code to work. I tried changing it to "Worksheet_Change2" but of course that never works haha. I mean it doesn't come up with an error now, but it also doesn't do anything at all. I don't know how to fix it when this happens. Any ideas please?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngToFltr As Range
    
    Application.ScreenUpdating = False

    If Not Intersect(Target, Range("J4")) Is Nothing Or _
       Not Intersect(Target, Range("G3")) Is Nothing Or _
       Not Intersect(Target, Range("G4")) Is Nothing Then
       Me.Protect Password:="password", AllowFiltering:=True, UserInterfaceOnly:=True

        With Me
            Set rngToFltr = .Range("B7:C" & .Cells(Rows.Count, "B").End(xlUp).Row)
        End With
    
        If Me.FilterMode Then Me.ShowAllData
    
        ' If month selected apply month filter
        If Range("E7") <> "" Then
            With rngToFltr
                .AutoFilter Field:=1, Criteria1:=Me.Range("E7")
            End With
        End If
        
        ' If Store criteria no Invalid ID apply Store filter
        If Range("D6") <> "" Then
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
            End With
        End If

    End If
    
    Application.ScreenUpdating = True

End Sub