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.
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:
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: http://www.mrexcel.com/forum/excel-questions/476078-counting-inventory-barcode-scanner-3.html
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.
Thank you everyone who took the time to read and especially for those who attempt to help.
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.
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:
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: http://www.mrexcel.com/forum/excel-questions/476078-counting-inventory-barcode-scanner-3.html
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.
Thank you everyone who took the time to read and especially for those who attempt to help.
