Keep track of value from another sheet base on time

[IgnBan]

Is it possible to use a formula to keep track of cell value in a sheet from another sheet base on the date and time input it?
This is what I?m trying to accomplish; I got a workbook where we keep track of inventory of items on several warehouses locations ?A?, ?B???Y? and ?Z?, these items are move constantly from warehouse location during the day. People moving these items not always input the movement right away, Ex. person 1 can move item 12 to warehouse location ?H? at 6:00 AM and not record the movement, then person2 can move the same item (12) to location ?S? at 9:00 AM and recorded it, then at 10:30 AM person1 come to the log book and record movement he made inputting below the 9:00 AM time with his 6:00 AM movement. As you can see the time and some time date of movement doesn?t follow a descending patter. My question is; How can I keep track in a workbook of the movement of items base on the most resent time input it?.
I made a sample workbook of what ours looks like. What I want is to keep in Sheet 1 ; Item Number and Item Location , on Sheet2 (this is the input log) ; Item Number, Time, Date, Item Location. Is it possible with a formula (or code) to when an item is enter in Sheet1 with an expression go to Sheet2 and scan the same Item Number and look for the most recent (date-Time) location?. And copy that location in Sheet1?..and if someone else come and inputs another time and location of more earlier time, update it?...and also can it be conditionally formatted in Sheet 2 so the most recent date-time input for Items be bolt ?

[misterewan]

try this..

Dim vOldVal 'Must be at top of module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim bBold As Boolean
    Dim xxx
    If Target.Cells.Count > 1 Then Exit Sub
    On Error Resume Next
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
    With Sheet1
        '.Unprotect Password:="Secret"
        If .Range("A1") = vbNullString Then
            .Range("A1:f1") = Array("CELL CHANGED", "OLD VALUE", _
            "NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE", "WORKSHEET NAME")
        End If
        With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
            .Value = Target.Address
            .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
                If bBold = True Then
                    .ClearComments
                    .AddComment.Text Text:= _
                    "Error:" & Chr(10) & "" & Chr(10) & _
                    "Bold values are the results of formulas"
                End If
                .Value = Target
                .Font.Bold = bBold
            End With
            .Offset(0, 3) = Time
            .Offset(0, 4) = Date
            .Offset(0, 5) = ActiveCell.Worksheet.Name
        End With
        .Cells.Columns.AutoFit
        '.Protect Password:="Secret"
    End With
    vOldVal = vbNullString
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    On Error GoTo 0
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target
End Sub

[Bob Phillips]

Here is an array formula that does it

=INDEX(Sheet2!$D$2:$D$200,MATCH(1,(Sheet2!$A$2:$A$200=A30)*(Sheet2!$B$2:$B$200=MAX(IF(Sheet2!$A$2:$A$200=A30,Sheet2!$B$2:$B$200))),0))

You will need to change the cell format to General from Text for the formula to take.

[IgnBan]

Thank you guys for the code and formula,
I'm working on the formula first, then I'll try the code, on the array formula, how can I modify it so instead of returning me a #N/A error, when the Item hasn?t make it to the warehouse yet will return me a ?Item no available in location yet? . I try to modify the IF statement, but can no make it to return me this string.
Thanks again guys

[Bob Phillips]

=IF(ISNA(MATCH(1,(Sheet2!$A$2:$A$200=A2)*(Sheet2!$B$2:$B$200=MAX(IF(Sheet2!$A$2:$A$200=A2,Sheet2!$B$2:$B$200))),0)),"",
INDEX(Sheet2!$D$2:$D$200,MATCH(1,(Sheet2!$A$2:$A$200=A2)*(Sheet2!$B$2:$B$200=MAX(IF(Sheet2!$A$2:$A$200=A2,Sheet2!$B$2:$B$200))),0)))

[IgnBan]

El Xid, thanks again, Can not make the latest formula work. Is this a new array formula or just an IF statement to eliminated the #N/A? When I enter the formula Excel ask me if I want to accept the modification because it found and error.
If I accept the error it gets rid off the #N/A error, but doesn’t return the value we are looking for.
Can you put it on the sample workbook to see if it give you the same error? One more question on the new formula if is a IF statement can I insert “Item not in List yet “ if the value is false, in another work I like the first formula, but it returns me an #N/A error if Item is not on the list, I would like to, if Item found return value, but if not found return the string “Item not on list yet”.

Thanks again,
Misterewan, I’m trying the code too, and will give you feed back too.

[Bob Phillips]

The problem is caused in posting to the forum, it inserts a space in the B200 in the second part of the formula. After pasting it, Excel tells you and asks if you want to accept its amendment, just say yes as Excel will correct it.

It replaces the other formula.

[IgnBan]

Thanks to El XLD and Misterewan for the contributions. I tried the formula and worked very well, but couldn't adapt the code. Misterewan if you can put your code in a sample workbook, so I can see how to make it wok. All I need is to in Sheet2 turn bold the latest (date-time) entry of a Item.

Thanks again to both of you!
Cid El Campeador, you formula is awesome!

[misterewan]

You must place the code i give you to "This WOrkbook" panel..just as is and the code will work..xori but im new here and dunno how to put attachments yet..thanx for the feed back..

[IgnBan]

Here is the example workbook, removed headers, tried with and without.