How to Auto-Fill Name and Time from a Badge Scan in Excel
Question details
The user needs to set up an Excel sheet that automatically retrieves a person's name and employer, and records the current time, when a badge number is scanned.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking attendance or logging entry by scanning a physical badge barcode into a spreadsheet to retrieve linked personnel data.
- Observed behavior
- Requires a lookup function to fetch information from a master reference sheet and a method to stamp the time of the scan without the time constantly recalculating.
Ensure your barcode scanner is plugged in and configured as a 'keyboard wedge', meaning it automatically sends an 'Enter' or 'Return' keystroke immediately after reading a barcode.
Use XLOOKUP to Retrieve Details from a Master Sheet
Set up an automatic lookup system that cross-references the scanned badge number with a master employee or student directory.
To make this work seamlessly, you will use two separate worksheets. One acts as a permanent database (master list), and the other acts as the daily scanning interface. The XLOOKUP function is ideal for this because it searches for the scanned badge ID and returns the corresponding data.
Create a new worksheet and name it 'Sheet2'. Set up columns for Name in Column A, Employer in Column B, and Badge Number in Column C. Fill this sheet with your complete roster of personnel and their corresponding badge numbers.
Go to 'Sheet1' and create headers: Badge Number (A1), Name (B1), Employer (C1), and Time (D1).
In cell B2 of Sheet1, type the formula: =XLOOKUP(A2,Sheet2!C:C,Sheet2!A:A,"Not found"). This tells Excel to look for the badge number from A2 in Sheet2's Column C, and return the matching Name from Column A.
In cell C2 of Sheet1, type the formula: =XLOOKUP(A2,Sheet2!C:C,Sheet2!B:B,"Not found"). Drag both formulas down their respective columns so they are ready for multiple scans.
Click into cell A2 on Sheet1. Use your badge scanner to scan a barcode. The scanner will input the number, move to the next row, and the formulas will instantly populate the Name and Employer.
Add a Static Timestamp for the Scan
Record the exact time the badge was scanned. Because standard time formulas recalculate constantly, a workaround is required for permanent timestamps.
Easily Manage Badge Scans and Lookups in WPS Office
WPS Spreadsheet fully supports advanced lookup formulas like XLOOKUP, VLOOKUP, and IF functions, making it perfect for building an automated badge scanning tracker for your organization or nonprofit.
- 1. Create a New Tracker: Open WPS Spreadsheet and start a blank workbook for your attendance or entry tracker.
- 2. Input Master Data: Create your master data directory on Sheet 2 with Badge ID, Name, and Employer columns.
- 3. Apply Lookup Formulas: On Sheet 1, set up your scanning columns and input the XLOOKUP formulas to reference Sheet 2.
- 4. Start Scanning: Click into your first Badge Number cell and begin scanning with your barcode reader. The data will populate instantly.
Frequently Asked Questions
What type of badge scanner works with Excel?
Almost any standard USB or Bluetooth barcode scanner will work. Computers treat barcode scanners exactly like external keyboards. When you scan a badge, the scanner simply "types" the badge number very quickly and usually presses the "Enter" key automatically.
Do I need to match every name with every badge number individually?
No. You only need to create a master list (database) on a separate worksheet once. The XLOOKUP or VLOOKUP formula will search the entire column automatically and match the scanned badge number to the correct person without any manual linking on the scanning page.
Why does my NOW() timestamp keep changing every time I scan a new badge?
The NOW() function in spreadsheet software is volatile, meaning it refreshes every time any data is modified anywhere in the workbook. To fix this, you must either enable Iterative Calculation to create a circular reference time-lock formula, or use a VBA Macro to paste the time as a static text value.
