Hi,
I like your youtube video on Automatically Timestamp Data Entries in Excel.
Can you help me further on how to capture Automatic Timestamp for Data update through formulas in Excel.
Would appreciate your response.
Regards,
Nitil
Hi,
I like your youtube video on Automatically Timestamp Data Entries in Excel.
Can you help me further on how to capture Automatic Timestamp for Data update through formulas in Excel.
Would appreciate your response.
Regards,
Nitil
Hi Nitil and welcome to the Forum.
If you don't want to use VBA to do the datestamps, you could put this worksheet function into a cell
=NOW()
which will show the current date and time. Problem is that that formula may change when the worksheet is recalculated. You can fix it by copying the cell then using PasteSpecial/Values.
Alternatively, enter a cell, then type Ctrl+; (which inserts the current date) then space then Ctrl+: (which inserts the current time). That "timestamp" will not recalculate.
Revision 1: Following the expalanation (and file) in your answer (that should have been a revsion to your Question really), it's trickier than I thought since the VBA Range.Precedents property can't be used accross worksheets.
In the attached file, I've taken the approach of moving the Worksheet_Change sub to the Data Sheet. When a change is made in the (now) shaded range of "Actual Data"(highlighted in bold below), I strip out the $ from the address of the changed cell and look for that in the formula of cells in the (now) shaded area of your "Formula..." sheet. Again I remove the $ signs from the formula and also use a mask to prevent C40 being mistaken for C4 (I guess you will have larger ranges.
I've also corrected the time stamping so that the first change (of cells listed in a cell formula) is recorded in column G (now headed "First change") then any other changes are timestamped in column H.
Here's the code (with Comments to aid your understanding):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myDateTimeRange As Range, myUpdatedRange As Range
Dim myDependRange As Range
TargAdd = "*'Actual data'!" & Replace(Target.Address, "$", "") 'strip out $ symbols from address
TargMask = TargAdd & "[!0-9]*" 'create mask to prevent longer addresses e.g. C40 not C4
If Intersect(Target, Range("C4:E14")) Is Nothing Then Exit Sub
Set myDependRange = Sheet1.Range("D6:F20") 'say where dependent cells might be
For Each myCell In myDependRange
NewForm = Replace(myCell.Formula, "$", "") 'strip out $ symbols from cell formula
If NewForm Like TargAdd Then 'if formula contains target...
If Not NewForm Like TargMask Then '..and isn't longer e.g. C40 not C4
Set myDateTimeRange = Sheet1.Range("G" & myCell.Row) 'do timestamps
Set myUpdatedRange = Sheet1.Range("H" & myCell.Row)
If myDateTimeRange.Value = "" Then 'Add first date
myDateTimeRange.Value = Now
Else
myUpdatedRange.Value = Now 'Or updated date
End If
End If
End If
Next myCell
End Sub
Please try it in my file (and later adjust macro to suit).
Hope this works for you
Hi John,
Thanks for your quick reply.
I think you didn't get my query.
I want to get timestamp for the cell which have formula.
There will be no change due to data entry, but there will be change in cell due to formula which is linked to another sheet/cell.
Hence, I am looking for the timestamp for the cell which have formula and there is no manual entry in the sheet.
The cell is updated through formula for which i require timestamp.
How to capture timestamp for the cell which have formula.
Hope you understand my query.
Can you able to help me resolve for my query.
Attached sheet for your reference.
Thanks,
Nitil
Hi John,
Can you please help me understand to action on below points.
'strip out $ symbols from address
'create mask to prevent longer addresses e.g. C40 not C4
Secondly, I require result same like below you tube video on Input time and Updated time
https://www.youtube.com/watch?v=gIPdx4-rqGg&t=19s
Thanks,
Nitil