Count empty cells if the previous cell is populated and timestamp

hsh5152

New Member
Joined
Apr 15, 2010
Messages
1
Hello,
I have a spreadsheet that i collaborate with multiple users on my office network. column A is for date, B for order number, C for Tracking number. I was wondering if there is a way where i can put the order numbers on daily basis and automatically show the time and date i made the entry.
Also once i put in the order numbers, i need to have a count of tracking numbers that are yet to be added for that order. If the tracking number is not added within 3 days of the order date i want it to show me that those orders are on "back order".
Please help !!!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Try this

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit
    Application.EnableEvents = False

    With Target
    
        If .Cells.Count = 1 Then
        
            If .Column = 1 Then
            
                .Offset(0, 1).Value = Now
                .Offset(0, 1).NumberFormat = "dd mmm yyyy hh:mm:ss"
            ElseIf .Column = 3 Then
            
                If Now > .Offset(0, -1).Value2 + 3 Then
                
                    .Offset(0, 1).Value = "Back order"
                End If
            End If
        End If
    End With

ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,508
Messages
5,572,573
Members
412,470
Latest member
nirortal
Top