Sep 30 2018 05:46 PM
I am trying to create a spreadsheet where when a specific cell is updated in anyway, the date/time stamp automatically updates in the cell right beneath the cell that was updated. I only need to have a one to one relationship with multiple cells. IOW, I have 5 cells in a row going across. When any one of the 5 cells is updated, I want an updated date/time stamp to appear in the cell directly below the cell that was updated. Is there a way to do this?
Sep 30 2018 07:17 PM - edited Sep 30 2018 07:19 PM
Hi Joseph,
This is possible, but you definitely need to a VBA code to do it!
Let's say the five cells you want to target are from cell A1 to E1, so please hover the Mouse over the Worksheet tab, right-click, and select View Code.
Then copy and paste this code into the worksheet code module:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Or _
Not Intersect(Target, Range("B1")) Is Nothing Or _
Not Intersect(Target, Range("C1")) Is Nothing Or _
Not Intersect(Target, Range("D1")) Is Nothing Or _
Not Intersect(Target, Range("E1")) Is Nothing Then
Target.Offset(1, 0) = Now
End If
End Sub
After that, save the workbook as .xlsm file extension to keep the code saved in it.
If you want to apply it to different cells, simply, you can change the cell ranges in the code and their order in the code does not matter!.
Hope that helps
Oct 01 2018 04:37 AM
Haytham - Thank you so much for the quick response on this. This works perfectly and I can see that changing the target offset allows me to change the location of the timestamp.
This is great and exactly what I needed. I really appreciate the help.
Oct 01 2018 07:09 AM
Thank you, but I have discovered a simplified version of the code as follows:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1,B1,C1,D1,E1")) Is Nothing Then
Target.Offset(1, 0) = Now
End If
End Sub
It works the same as the previous one, so I recommend you to use it instead.
Regards
Oct 01 2018 07:15 PM
Thanks for the simplified version. One more question for you, I tried replacing "Now" with "Today" to get just the date but that didn't work. How can I change this so it only shows the date if needed?
Oct 01 2018 08:20 PM
Hi Joseph,
There is no function in VBA called Today, the VBA function equivalent to the Worksheet function Today is Date.
So you have to use Date instead of Today as follows:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1,B1,C1,D1,E1")) Is Nothing Then
Target.Offset(1, 0) = Date
End If
End Sub
After you apply this change, you may get the date in the worksheet like this:
If you encounter it, don't worry, it's just a format, and you can change it to a short date from the Home tab as follow:
Hope that helps
Oct 02 2018 07:16 PM
Thanks again. You have been a huge help here. So I have one more question on this. The macro works when I manually update the cell, the date plugs in just perfect and it updates every time I update the cell. However, I was hoping that this would also work if the cell was updated by input into another cell.
For example, if A1 has data and A2 is where the date updates automatically, I want to have a formula in A1 that says "=Sheet2:A1". I then enter data into Sheet2:A1 and that data also updates in A1 on the original sheet but the date does not update in this case.
What I am ultimately trying to do is create a sheet that updates the date when certain fields are updated but I want those fields to be updated by inputs from another sheet. I hope this makes sense.
Is there a way to do this or does the macro only run if the cell is updated manually on that sheet?
I appreciate any help you can provide.
Oct 03 2018 10:43 AM
Hi,
In this case, you should inject the Sheet2 with the same code but with some changes to move the time stamp to the right place in Sheet1!
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Sheet1.Range("A2") = Now
End If
End Sub
The above example for the Cell A1 in Sheet1 which has a formula referred to cell A1 in Sheet2 (=Sheet2!A1), and you need to put this code in the Sheet2 code module NOT in Sheet1.
If you have more than one cell referred to Sheet2 such as cell A1 and B1 in Sheet1, you have to duplicate the IF statement in the same code of Sheet2 to check each one separately:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Sheet1.Range("A2") = Now
End If
If Not Intersect(Target, Range("B1")) Is Nothing Then
Sheet1.Range("B2") = Now
End If
End Sub
Apr 17 2019 12:53 PM
@Haytham Amairah Hi I too am looking to have the date updated in a cell when the designated cell is changed I placed the VBA code in the sheet code tab but what do I do on the sheet itself? Any help is greatly appreciated thanks
Apr 19 2019 11:47 AM
Hi @Deleted,
I think you need to change the cell references in the code to comply with existing data in your spreadsheet.
Apr 19 2019 12:52 PM
@Haytham AmairahThanks Haytham, ill give that a try
Apr 15 2021 03:21 AM
hi
can you please tell me how can i change from lines to collum? i'm trying to do the same thing but instead of changing specific rows, i'd like to everytime i change a collum cell it brings the now date on the right cell :(
Apr 15 2021 12:24 PM
With the permission of everyone, here is another solution without VBA.
Simply with a formula.
=IF(C1="","",TODAY()) in english
=WENN(C1="";"";HEUTE()) in German
Info in the inserted file.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Apr 15 2021 12:27 PM
Apr 20 2021 10:11 AM
Apr 20 2021 11:15 PM
I cannot follow your flow of thoughts.
Please attach a file (without sensitive data) and on the basis of this you explain your plan in detail.
It makes no sense that we are constantly producing files that are not what you imagine.
It is good to read this information in advance:
Welcome to your Excel discussion space!
Thank you for your patience and understanding
Nikolino
Jul 05 2021 08:28 PM
Sep 04 2021 02:36 PM - edited Sep 04 2021 02:41 PM
No VBA needed; and that would not work when the workbook is opened in the browser or mobile platforms.
If the column where you want to automatically set the current date is say B, the column where the data is being entered is say C, and you are starting from B1 then therre are two variants:
These work when the workbook is opened from Windows or Android (so it probably also works on IOS, but I don't use that platform, so can't verify).
Note: There was a previous reply which was similar to the first option above, but used TODAY(). That won't work, because the TODAY() function updates whenever the workbook is opened. NOW() will not do that, it "freezes" the date when invoked.
Sep 04 2021 03:02 PM - edited Sep 04 2021 03:15 PM
See my reply below, but use this instead of the first version:
=IF(COUNTA(C1:Z1)=0," ",NOW()).
Adjust for however many columns you want to cover in the row.
Sorry replied to @NikolinoDE instead of @Carly_Forrester.
Oct 01 2021 09:01 AM
I have a similar problem. I'm trying to update one cell in a row (column G) if any other cell in that same row is modified (columns range from A to R). So for example, in row 3, if cells A3:R3 are modified (number or text or dropdown menu change), then cell G3 updates to that day's date only (no timestamp). Any help is appreciated!