Assistance with Excel for a semi-automated Attendance Tracking System with validation(s) required

Ian Kok

New Member
Jul 21, 2015
Hi everyone,

To start off, my knowledge of coding is minimal and I do not have knowledge of this field. What knowledge I have is rudimentary at best though I do know how to use Excel, functions, pivot table etc. :oops:

My needs are as below and I hope I be able to find someone here who can assist me with my issue. My company wish to implement a attendance tracking system with minimal cost and as such we are exploring Excel to do what we need and to attempt to eliminate or reduce human error in the process.

System: Windows 7, Microsoft Excel 2013

What I have are attendees who each have a unique identifier which we are implementing a barcode scanning solution for. These unique identifiers will then be used to identify the attendees in the excel spreadsheet for their attendance.

My issue lies in several aspects: :mad:

1) The unique identifier might not have a corresponding value in a master table in the second spreadsheet, hence the code needs to be able to check for a corresponding value and if it fails to be found, prompt the end-user to key in the attendee particulars such as Name, Nationality, Address etc. It be ideal if this information that is keyed in is that automatically copied into my master table so that if this same attendee comes again in the future, the records are already in the master table without any need for human intervention (ie: copy and paste or updating the master table)

2) I may have 10 different courses running concurrently on any one day and when I scan the unique identifier into the spreadsheet, a prompt needs to come up to prompt the end-user to select the course that is being attended by this attendee plus capture the date and time that the attendee is attending this class for.

3) Some of the courses can be run over the course of a week and we might restrict the attendee to attend say a maximum of 3 classes before the next run of courses is conducted so we need the code to be able to prompt if the unique identifier has already either attended this course before in this same stretch of period when we are conducting these courses or if the unique identifier has already attended max of 3 classes during this run of courses. An option to force-through the system to accept the attendee attending a 4th or more class be ideal as well despite having the warning or prompt in real time

4) The ability to add in additional free text columns despite the coding that is used (no idea if this will impact any potential coding hence am sharing my needs)

I found a similar thread whilst browsing for a solution which can be found here and which address part of my needs in (1) above but was unable to get the additional code as shared later on in the thread to do the automated copying over to the master table and I still need a solution to address my needs from (2) - (4). This is the link of that thread:

The layout I am open to exploring but should look something like the below when printed out?

Unique Identifier | Name | Nationality | Personal Particulars | Course Name 1 | Course Name 2 | Course Name 3 | etc | Free Text |
12345 | Sample | Singapore | ABC 123 | X |Date/Time | | X | Date/Time | | ABC123 |

I hope someone is able to help me with this and a BIG THANK YOU in advance to anyone for your help. I realize too that all that we are looking for might not be doable but will appreciate your advice in which is not able to be done and which can be or if I can be pointed in the right direction to begin. :biggrin:

Thank you everyone who took the time to read and especially for those who attempt to help.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...