Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Logbook - Confirm when item not returned yet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Logbook - Confirm when item not returned yet
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,Worksheet 2Columns,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. "Steve Jackson" wrote in message . uk... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Logbook - Confirm when item not returned yet
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 "Steve Jackson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the returned value to look up an item | Excel Worksheet Functions | |||
anyone have sample of IT daily logbook? | New Users to Excel | |||
Pilot Logbook... | Excel Worksheet Functions | |||
Pilot logbook! Help, someone...? | Excel Discussion (Misc queries) | |||
More Pilot Logbook Help.... | Excel Worksheet Functions |