Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Automatic Timestamp for Data update through formulas in Excel

0

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

Answer
Discuss

Answers

0
Selected Answer

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 

Discuss
0

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

Discuss

Discussion

Nital

Please see Revsion 1 to my aAnswer (and attached file).

Being new to the Forum, you might not have realised that your Answer above isn't an Answer to your question (but a response to a possible Answer). Next time, please make minor comments against the Discussion under an Answer or revise your original Question to add a file or give clarification of the real need.

Also, only Select an Answer if it answers your question (as much as possible).
John_Ru (rep: 6092) Feb 9, '21 at 7:09 am
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
NitilA (rep: 14) Feb 9, '21 at 8:44 am
Nitial. 

Again your point (and any response to this) have be in the Discussion to my Answer (not here) since other users may follow a Selected Answer to solve their problem).

On the first point, the. Address property returns a cell address with $ e.g. $C$4. Your formula may refer to the cell with one or two $ characters (to fix row and/or column) so I remove the $ from both the address returned in Data Input and in each formula (to make the comparable).

On the second point, the Like operator would detect... C4 in C40 so I add the wildcard [! 0-9] to check that the following character is NOT (the ! does that) a number from 0 to 9 (e.g. the 0 in c40 when search g for just C4).
On the video, I'm not sure but think that does what I've done. Don't have time to check for a few hours yet. 

Does the macro work in my file? It should add a date in G if you change C4 in Actual data then one in H if you change it again (and in H as it is done again or for any ce where the formula in that row use the changed cell). 
John_Ru (rep: 6092) Feb 9, '21 at 9:48 am
Nitial.
  
Not sure what you're aiming to achieve here.  

From the YouTube video, I now see that you appear to want the datestamp to appear/ be revised in your Formula... sheet when several cells are completed (and then later changed) in the Actual Data sheet. Is that right?

Will you formulae be in the other sheets akeary?

I think I've done what your question said and that you need to work out your needs and pose them in another question. 
John_Ru (rep: 6092) Feb 9, '21 at 11:18 am
Thanks John for your support
NitilA (rep: 14) Feb 11, '21 at 2:54 am
Nitial. Not sure if my support helped but if my file /code work for you, kindly mark my Answer as Selected. 
John_Ru (rep: 6092) Feb 11, '21 at 3:01 am
Nital. Thanks for selecting my Answer. 
John_Ru (rep: 6092) Feb 11, '21 at 3:26 am
Hi John,
I want to write this below code in module.
I see error of "424 Object required" when copied the code and run in Module.
Hence, Can you please help me with the correct coding or any changes required for the same 
Private Sub Worksheet_Change(ByVal Target As Range)   Dim myTableRange As Range Dim myDateTImeRange As Range Dim myUpdatedRange As Range   Set myTableRange = Range ("A2:D10")   If Intersect(Target, myTableRange) Is Nothing Then Exit Sub   Set myDateTimeRange = Range("E" & Target.Row) Set myUpdatedRange = Range ("F" & Target.Row)   If myDateTimeRange.Value = "" Then   myDateTimeRange.Value = Now   End If   myUpdatedRange.Value = Now   End Sub
Regards,
Nitil
NitilA (rep: 14) Feb 19, '21 at 12:42 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login