External data constantly updating, how do i retain a certain value??

I have an access control system which logs all card swipes into a microsoft access database. I have set up an Excel spreadsheet, which imports the following field data: date, swipe device (ie. door-in, door-out), surname and firstname., and updates the spreadsheet every 60 seconds.

This works ok. However, on a seperate sheet within the spreadsheet, i have a list of employees. next to these, i want to display the current in or out status.

I have created a string in the cell next to "fred bloggs'" name saying: if device=door-in and surname=bloggs and firstname=fred then cell content = "IN".

or

if device=door-out and surname=bloggs and firstname=fred then cell content="OUT".

however, with the data sheet constantly updating, the above expressions only relates to the first row of data, and when a different person swipes in or out, the data moves down the sheet, and the new swipe data is inserted at the top, therefore the expresion is no longer valid.

Therefore, what i am trying to do is, if device=door-in,name etc. cell content will stay as "IN" untill a "doorout statement appears" and only then will the cell content = "OUT"

So how do i retain the data untill certain data appears?

I would greatly appreciate somebody's help

Many Thanks

Steve