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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-05-07T18:09:14+00:00

    Hi Debbie

    Find in the link below the file with the update according to your last requirement.

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

    re:*"...*is there any way to auto clear the spaces if people forget to punch out?..."

    Please, follow the steps in the picture

    3- To select multiple cells (seats) in column A press and hold CTRL key 

    AFTER

    The workbook before it close will perform a macro to clear and update the data entry in the file

    On the Log sheet It loops thru all ID and those who DID NOT PUNCH OUT at the lunchtime will be marked

    I hope this helps you 

    Regards

    Jeovany

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-05-07T01:13:34+00:00

    3-**We realize that there are people not on the database, because they have no badge.

    A:= Suggestion: Give them a temporary number ID and update the "Employee Database", if no badge is required then you can ask them their number ID and manually type it in ScanID cell and that's it.

    4- Also, is there any way to auto clear the spaces if people forget to punch out? 

    .

    I was waiting to see what Jeovany had to say.

    .

    #3

    If it is not possible to give them a dummy ID # in the DB, someone will have to access the Seats tab, and manually enter their name and time in a seat.

    .

    Alternately, let people without ID cards manually enter a dummy ID in the scan page

    #4

    You would have to add a new button for "manual clean" where they enter their dummy ID on the scan page and then click the "clear" button to clear the entry.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-05-06T21:38:33+00:00

    Hi Debbie

    Apologies for the late response. I've been busy

    Regarding your questions

    1-The box with all the seat allocation stopped refreshing. So even if there was nobody in the seat, it was red. However, the assignment was still working fine.

    A:=Please, check if any new column or row was inserted in sheets "SCAN" or "SEATS".

     The change of color is due to Conditional Formatting rules.

    2-**We got an error saying the changes from the other workbook could not be merged, I guess this is more of a SharePoint issue.

    A:= Very likely is a SharePoint issue, but and I don't know much about it. Unfortunately, I can not help you in this regard

    So you should take this issue to the IT department in your workplace. They might give you a solution or a better explanation of your question.

    3-**We realize that there are people not on the database, because they have no badge.

    A:= Suggestion: Give them a temporary number ID and update the "Employee Database", if no badge is required then you can ask them their number ID and manually type it in ScanID cell and that's it.

    4- Also, is there any way to auto clear the spaces if people forget to punch out? 

    I will come back later with the solution.

    Regards

    Jeovany

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-05-05T16:55:59+00:00

    Hi again Jeovany! 

    I have a couple questions. 

    We tested the excel today in the cafeteria and we encountered a couple situations and I wanted to tell you so you may guide us. 

    We are using two computers with two RFID scanners. 

    At some point, the following happened:

    1. The box with all the seat allocation stopped refreshing. So even if there was nobody in the seat, it was red. However, the assignment was still working fine.
    2. We got an error saying the changes from the other workbook could not be merged, I guess this is more of a SharePoint issue.
    3. We realize that there are people not on the database, because they have no badge.

    Also, is there any way to auto clear the spaces if people forget to punch out? It happened a lot :(

    Was this answer helpful?

    0 comments