Is Vlookup the right answer?

[Livin404]

Good evening,
I have an equipment log, I'm trying to update the date in/out on an equipment status sheet. Once the employees selects "Sign Out"/"Sign In" on the Macro the information will be uploaded on to either a Inbound or Outbound Sheet. I provided a snip-it from the "Outbound". As more are put in, the rows starting at Row 4 shift down. What I'm trying to accomplished is to have the latest date reflect on the equipment sheet. I tried using a Vlookup based on Row 4 Columns G:H and yet again on E:F. You will be able to see what I attempted on the bottom of the image. There will only be one Date in/Date Out for Each Tool #, and that would be time it was signed out or in.

Thank you

 

[Misca]

Looks like your data set might have several time stamps for both in and out for the same machine. VLOOKUP only returns the values from the first matching row. You'd be most likely interested in getting the values from the last matching row. The new XLOOKUP function can do that.

Since your return values are dates / time stamps (=numbers) a simple MAXIF(S) should do as well. For the last In Date you might want to make sure it's equal or greater than the last out date.

Then again, since you're already using VBA you could use VBA to get the records from the last found record as well.

For both formulas & VBA you'd make it easier to keep up with the ranges if you turned your ranges into tables. This way the ranges would be dynamic and always up to date.

Also, you might want to consider using just one table / sheet with two date columns to handle both outbound & inbound data. If I've understood you correctly the Inbound & Outbound data is basically the two sides of the same coin.

With this setup a simple Pivot Table should be all you need for your equipment status sheet: Just drop the in & out dates to the values field and use the Summarize Values by Max. If you did this you might want to use Conditional Formatting to hide the Sign In values that are smaller than the sign out values. Or if you're using Power Pivot you could use DAX to return a null if the Max Out is greater than the Max In value.

 

[Livin404]

Thank You for your response. You’re exactly right about it being two sides of the same coin. I dabbled in all your things that you mentioned. I’m assisting someone who ask for a little help, and I don’t want to change his product on its head since he has a deadline, but I will definitely rework it after the suspense when the pressure is off. He did a recording of his macro which is something I don’t do.
Having said that what would be great if you know of something where I can have the most recent date for the signed out/in copied over to the inventory sheet. Only one date so the most recent date would replace the previous date.
I thought something like an “If”statement = a part number then the date from Row 4 (middle image on attachment).would go to the inventory sheet (bottom image). It would only be that Row 4 on sign in/out sheets would be the source since every time an activity happens that row 4 is where I’m going to get my time.

thank you

 

[Misca]

I'd go with the MAXIFS if the Pivot Table option is out of the question. Just use the date as the Max_Range for the Date Out column. You could use basically the same formula for the other way as well but I'd wrap it in an additional IF statement making sure the max Date In value takes place after the Date Out. When it does the tool is still out.

 

[Livin404]

That worked well, I did some looking into it and I got something like {=MAX(IF('Sign out '!$C$4:$C$1048576='Tool Data'!B3,'Sign out '!$E$4:$E$1048576))}. I know it's right, but I'm left with one annoying problem. Every time I select the sign-out or sign-in the log (the image is in the middle on the attachment) shifts down one row. That list is ever changing. I tried messing around with the hold signs $ in the formula hoping that may help. Do you have any recommendations? Thank you again for pointing me in the right direction.

 

[Livin404]

I notice on the formula the first value in Column E keeps shifting because the rows in the Sign out keep shifting to allow for a new entry. Sign out '!$E$4 becomes a Sign out '!$E$5, so it never gets updated. At least I know I got the basic formula correct.
Thank you.