Automated date/timestamp without affecting other cells with date/timestamp

[Stephen_Santos]

I am having trouble to find a way to automatically date/timestamp a cell after entering data from another cell. (I entered automatic timestamp using a formula but did not work the way I wanted it)

Another problem I'm running into is when automatically timestamping cell B2 the cell B5 with the timestamp changes. Is there a way I can get a certain cell to have a static timestamp?

Also, after the data has been entered in B2, I want to move to the next box which is B4. Is there a formula for me to do that?

See picture below

Thanks in advance for the help!

 

[Joe4]

Welcome to the Board!

I think the kind of date/time stamping you want to do will require VBA.
Are you open to a VBA solution?

If so, does you data always follow the pattern you show above? i.e.
Row 1: Box ID
Row 2: Date/Time
Row 3: Blank
Row 4: Box ID
Row 5: Date/Time
Row 6: Blank
Row 7: Box ID
Row 8: Date/Time
Row 9: Blank
etc.

Is there any end, or could this pattern continue on indefinitely?

 

[Joe4]

If all the answers to my questions above are yes (and pattern can continue on indefinitely), here is a VBA solution for you.
First, right-click on the sheet tab name at the bottom of the screen and select "View Code".
Then copy and paste this VBA code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once (i.e. row deletion)
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see that the updated cell is in column B and column A is "Box ID"
    If Target.Column = 2 And Target.Offset(0, -1).Value = "Box ID" Then
'       Update date/time stamp in cell below
        Application.EnableEvents = False
        Target.Offset(1, 0).Value = Now()
        Application.EnableEvents = True
'       Move down 3 rows
        Target.Offset(3, 0).Select
    End If
        
End Sub

This will automatically populate the date/time stamp in your cells whenever you manually update column B on a "Box ID" line.

 

[Stephen_Santos]

@Joe4

Thank you for the help!

Is there a way where I can input that same type of VBA on this work sheet below? It'll be the same exact thing where I would need the static timestamp after the last data for the Lot ID area has been entered. Also, after the data has been entered on Box#3/Batch#1 area I would like to automatically move to the next cell which will be cell E1. Basically after each batch has been finished I would like to go on to the next batch over until all batches have been completed. I hope the picture below can explain it better.

Also, if I share this worksheet on sharepoint, will anything be affected if someone was to open the worksheet?

 

[Joe4]

Macros/VBA do not work on SharePoint or in Excel Online.
The files would have to be downloaded to the person's computer or network.