Create a seat assigner with Excel and RFID

Anonymous
2020-04-16T23:26:27+00:00

Hi, 

Due to Covid19, we are tracking who sits with who in the cafeteria.

We have a simple Excel file that assigns a seat in the cafeteria to the employees based on the RFID. It is very simple, We have a Seat column with seats from 1-135, they scan their badge, the ID number appears on the ID column, then there is a name column with a Vlookup based on the ID that enters the name of the employee and that is it. 

The issue we have is that, sometimes it assigns a seat that is still taken. I cannot think of a way in which the employee scans their badge again and the record is deleted from the seat number so that it is now free for someone else :S and doing that without losing the original records of where the person sat. 

Any ideas would be highly appreciatted

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2020-04-23T05:33:48+00:00

@ Debbie.

I have no scanners here, so I typed in the ID numbers

1- Before the start

The SCAN sheet will look like this, The scanner will be focused on cell C3 Named range ScanID 

2- After the first 10 scan (employees)

After the first 10 scan (employees) on each sheet 

RECORD/TRACKING before any check-out.

3- After some of the staff check out

Conditional Formatting is applied to cells 

the ones in Yellow shows those who are taking more than 30 min in their lunchtime and obviously haven't been checked out.

Hope this helps

Regards

Jeovany

Was this answer helpful?

4 people found this answer helpful.
0 comments

50 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2020-05-15T02:25:08+00:00

    Thanks for your quick answer! 

    Question: 

    Since we have the files on SharePoint,we are struggling with an error that says it could not merge changes and save, so it closes and reopens the file. Does that mean that if this happens, the macro will mark as "not punched out" all of those that were blank at the moment of the error?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-05-15T06:21:05+00:00

    Thanks for your quick answer! 

    Question: 

    Since we have the files on SharePoint,we are struggling with an error that says it could not merge changes and save, so it closes and reopens the file. Does that mean that if this happens, the macro will mark as "not punched out" all of those that were blank at the moment of the error? 

    Unfortunately, I can not give you an expert opinion regarding SharePoint issues.

    Anyways, I removed the macro from the workbook event and created a Command Button you would now have to click before closing the workbook to mark as "not punched out" all of those that were blank at the end of your working day.

    Here the link with the updates

    https://1drv.ms/x/s!AjGRD1TlwpAGlniwIxOU1dlDFhbK?e=uneb2E

    Regards

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-05-15T22:42:56+00:00

    Thanks a lot!

    Yes, it seems that macro enabled books are not friends with co-authoring :(

    Thanks for the update!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-05-28T21:59:44+00:00

    Hi Jeovany, 

    I am so sorry to bother with this again. But we were asked for something else :(

    The system is awesome and it has worked a lot; however, since it is saved in a SharePoint, it keeps giving us errors because we have to workbooks opened at the same time.  

    Is it possible to have the seats clear by themselves after a specific amount of time? That would help us because we would not need people to punch out.

    If the answer is yes, is it possible to establish the amount of time to clear the seats based on schedules? For instance, 7am-10 am 30 minutes and 11-2pm 1hour? (this is just optional, it would be better to control the space)

    Was this answer helpful?

    0 comments