Logbook - Confirm when item not returned yet

[Steve Jackson]

I have 2 worksheets - 1 worksheet lists the items in stock. Columns are
Items number, Item, description. Each item number is unique. The 2nd
worksheet is a logbook of all the times someone has booked out an item as
well as the current items booked out but not returned yet. Coolums are items
number booked out (same as first worksheet), user who it out, date booked
out and date returned.

I want to add a colum in the first worksheet (item list worksheet) that
looks at the item number column and checks it against all the rows in
worksheet 2 to easily tell me if an item is currently booked out or not. I
want to create a formula that basically looks at all the rows in worksheet 2
(the logbook) and sees if the booked out date is completed but the booked
back in date is not completed (i.e the user has yet to return the item). I
can create a straight forward formula to say how many times an items has
been booked out but not whether it is actively booked out.

Any ideas?

Any help will be much appreciated.

Steve

 

[Steve Jackson]

I have tried to use the ISBLANK function along with the VLOOKUP to see if
the booked back in date is blank (i.e. the item has not been booked back
yet) but this does not work because there are multiple times an item can be
booked out and it seems to check just the first time the item number
appears.

=IF(ISBLANK(VLOOKUP(Worksheet1ItemNumber,Worksheet2Columns,ReturnedBackDateColumn,FALSE)),"Yes","No")
- This formula looks at the cell with the item number, finds the occurrence
in worksheet 2 and sees if the returned back date is blank. Problem is that
it checks the rows in worksheet 2 from top to bottom and if an item has been
booked out and returned it will check that row before getting further down
to abother occurrence of the item being booked out and potentially not
returned.

 

[Mike H.]

I think it would be much easier to do this with a simple vba macro than to
try to write a formula to do it. Here is something that works and you could
modify it to update all the items not just the current one. If you turned of
screen refresh it would run VERY fast.

Sub getopendt()
Dim item As Variant
Dim x As Double
Dim Itemrow As Double
Dim itemcol As Double
Dim Status As String
Dim checkout As Date
Dim checkin As Date

'assume cursor is on the item # you wish to check on when the macro is
envoked.
Let item = Cells(ActiveCell.Row, ActiveCell.Column).Value
Let Itemrow = ActiveCell.Row
itemcol = ActiveCell.Column

Sheets("sheet2").Select 'assume sheet 2 has the login-logout info.
Cells(1, 1).Select ' go to top row
x = 1
Let Status = Empty
Let checkout = Empty
Let checkin = Empty
Do While True
If Cells(x, 1).Value = Empty Then 'assume that if col 1 is empty you're
done checking
Exit Do
End If
If Cells(x, 1).Value = item Then 'item # in first col.
If Cells(x, 3) = Empty Then 'date returned in 3rd column
Let Status = "Item check out on " & Cells(x, 2) & " and not yet
returned"
Else
Let checkout = Cells(x, 2)
Let checkin = Cells(x, 3)
End If
End If
x = x + 1
Loop


Sheets("sheet1").Select
Cells(Itemrow, itemcol + 1).Select
If Len(Status) > 0 Then
ActiveCell.Value = Status
Else
Let ActiveCell.Value = "Item checked out on " & checkout & " and
returned on " & checkin
End If
End Sub