Need Help with a Tracking System/Database

[RyFlyBones]

I'm tasked with creating a tracking sheet to determine which employees complete which training. So far I have used drop down lists to display the necessary information and make things cleaner on the sheet meant to display the information. For employee names I need to display their appointment/expiry date, title and region of work; for the training I need to display the type of training, the subjects, the individual lessons, the region they are offered and delivered, the starting and end dates, and the language it is offered in. So far I have been able to display all this information in a need table with drop down menus and using the =VLOOKUP formula. When displaying the information I separated the employee's information (and the information along with it) from the training information. The final goal is to be able to select the employee's name and for it to display all of the employee's information, then to select the training type, subject and lesson and for it to display if the employee has completed it or not. Another goal is to able to reverse search by looking up the training type, subject and lesson, and seeing which board members have completed it. I've tried my best to figure this out and have googled a lot, but I'm stuck at combining the employee and training information and stuck on how to display completion. Hopefully my excel sheet isn't too complicated to understand, I separated sheets from data and display and fiddled around with a sheet for data entry to mark completion. I censored all the information in the sheet and put generic names as placeholders to give a sense of what kind of numbers I am working with. Any help is greatly appreciated. Thanks,

[mick0005]

I will probably be able to help you, but I am not 100% clear on what you want as your output. Which sheet/cell(s) are you hoping for the results to be.

I think for the 1st question, you want it to be in Cell I11 and it would result simply in a "Y" or "N"?

For the 2nd question, where do you hope to have the results of who has completed something after selecting the training course?

[mick0005]

If I am correct on what you want for Cell I11, here is the formula for cell I11.

This assumes that the "Data Entry" worksheet you set up will be used to capture the records of when an employee completes a training. It assumes that if the record does not exist for the selected member, type, and subject then it will result in "N", since if the record doesn't exist, I assume they did not complete the training.

Note, this is an ARRAY formula. You need to execute this formula from the function bar by hitting CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

For your 2nd wish, I have another array formula. It makes a few assumptions:

1. I formatted the data in "Data Entry" as an actual table. I am using table nomenclature in the formula.
2. Search entries for Type, Subject, and Lesson are on the Data Entry worksheet in cells E23, E24, & E25, respectively.
3. Name results appear in cells D28:DXX where XX is equal to however many rows you want to drag this down based on number of expected results.

Formula for the actual results is an array formula which requires again, CTROL_SHIFT+ENTER when you execute it. You can then drag it down as far as you need to.

Please Login or Register  to view this content.

Training tracking database - array for training results.xlsx

[RyFlyBones]

Wow! Did not expect this kind of clarification and help. Thank you so much, I will input my data and see if everything still works smoothly. I'm still not quite sure about how often I need to press CTRL+SHIFT+ENTER when executing an array formula since I've never used one before. Also, on the chart in the data entry section for the completion do I have to input Yes for every training for every employee individually and leave the data up or does it automatically remember what was inputted the first time so I can select another employee from a drop down list without losing the data from the employee I just completed. I'm not sure if that makes too much sense. I'll try to clarify: in the 'Data Entry' sheet, in the tblTrainingRecords table, there are 83 names to input each having 2 types of training, 8 subjects and many lessons, do I need to input each name with each training or can I select my filtering from the in-cell drop down menu and then input a Y or N so when i choose another training for the same member in the drop down menu the old training remembers whether I inputted a Y or N. Let me know if that made any sense. Thanks a lot for the help!

[RyFlyBones]

Also, when changing the names the arrya formula seems to stop working, I made sure to change the names of cells accordingly too (ctrl+f3) to match. Is there any modifications to the formula I need to make after switching the data back to the real names and information?

[mick0005]

Relative to CTRL+SHIFT+ENTER, you only need to do that for the first cell (click in the function bar, click somewhere into the formula, and then hit CTRL+SHIFT+ENTER) Once you have executed the formula the first time, you can drag it down. It may tell you that you cannot change part of an array, this would happen if you don't drag the formula down at least to where the existing formulas extend or beyond. If this happens and you don't want to extend the formula down that far, just copy the formula, delete it from all of the cells, and then put the formula into the first cell again and C+S+E to execute it. Then you can drag it down as far as you need.

For the data entry table, I did not build any kind of function into the table to bring in the previous records' entries. This could be done with a macro if it was required, or alternatively you can just copy the row from above and paste it into the new row and change the fields you need to change.

I am not sure if I understand correctly or not, but you can easily have it automatically have "Y" as the default entry by putting in a formula like:

Please Login or Register  to view this content.

Since this is a proper table, the formula will automatically extend down to the bottom of the table and when you add new records/rows, it will automatically be populated as well.

This works based on inputting data in a tabular format, with each record being unique and with each record requiring all fields to be populated. So, if you have 2 types of training with 8 subjects (2x8 = 16) with lets say 5 lessons each, then you would have 80 records (rows) for each employee (16x5 = 80).

There shouldn't be any trick to the array... I went and changed the names in the Data Entry sheet in tblTrainingRecords and there was no issue with the new names showing up in the search results. Maybe you can share a worksheet with any changes you made (still without sensitive information) and I can take a look.

[RyFlyBones]

Thank you, I think I understand the ctrl+shift+enter need now. As for the macro, I've nerver worked with them so I guess I will have to amnually input every single emplyeeand training. I changed the "John Doe's" backt he the actual names and the training names back to the actual names but now when I input the Y or N in the data entry it is not showing up in the display cell at I11 on the first sheet. Any idea why it suddenly stopped working?

[RyFlyBones]

Nevermind about the last concern, I figured it out. I just repasted the formula and hit ctrl+shift+enter to activate it again and it worked.

[mick0005]

Also, for cell I11 on the Display worksheet, I changed the formulas to use table nomenclature so that they will still work if you extend the ranges beyond what was done in the sample file.

Change it to this, and CSE to execute.

Please Login or Register  to view this content.

[RyFlyBones]

Thank you so much, you've been great help. I'm new to this forum is there any way on this site where I can thank you and give you some type of "reputation" or thank score, other than "add reputation". If not, well thank you for the help and I will definitely look into arrays for future projects since they seem to be helpful.

[RyFlyBones]

One last thing before I finish this up, is there any way to move the search table on the last sheet to the first sheet without changing the formulas?

[mick0005]

If you want to move the search fields themselves (where the user selects the values to search from in the dropboxes) just select the entire range, cut it, and then paste it elsewhere. The references in the results array formula will dynamically update based on the new location.

After you have relocated the search fields (and so now the references in the results formula have updated), to move the location of where the results show up, simply take the formula which currently resides in cell D28 on the Data Entry worksheet (the first instance of the results array formula), copy the text from the function bar, and then paste that text where you want the results to be now. Drag it down and you're all set.

[mick0005]

is there any way on this site where I can thank you and give you some type of "reputation" or thank score, other than "add reputation".

Just use the "add reputation" button.

[RyFlyBones]

Sorry for always asking for new things but a new thought came into my head that would make things a lot easier. Instead of having to input a 'Y' for every single lesson for each employee, is there a way to have the option to input a 'Y' for the subject and it autofills all the lessons to 'Y'. That way we can quickly input the employee's completion of training subjects and if for some reason they missed a specific lesson we can go change that specific lesson to a 'N' while the others remain a 'Y'. If it's too complicated then that's fine you've been more than enough help already, I'm just trying to optimize the easiest way to go across this. Again, Thank you for your help.

[mick0005]

If you want a macro to create a copy of the last record, here is one I basically just created using the macro recorder (using relative references).

1. Hit ALT+F11 to open the VBA editor.
2. Click Insert in the menu bar, select Module (to insert a new module)
3. In the new module that opens, paste the following code:

Please Login or Register  to view this content.

Now you can close the VBA editor. Save your work now just incase the macro has unexpected results.

Hit CTRL+F8 to bring up the run macro dialogue box. Select the macro and choose options button on the right and assign a keyboard shortcut to it.

Click in the last row with tblTrainingRecords that has data in it, then hit your keyboard shortcut for the macro. It will copy the last row of data and paste it into the new row. Then you could just change the fields u need to change. I guess it will save you a little bit of time.

[RyFlyBones]

Does this macro simply copy the above information and paste in the below cell? What I was suggesting was to have an option to input 'Y' at the Subject's section as well as the Lesson's section so if all the lessons were completed I can simply input a 'Y' into the subjects section and it will autofill all the lessons with a 'Y'.

[mick0005]

is there a way to have the option to input a 'Y' for the subject and it autofills all the lessons to 'Y'.

I can't think of an easy way to do this offhand to be honest. The way I'd probably go about getting your current data all translated into this new format is to just create the records for one employee (all types, subjects, and lesson combinations possible) set them all to "Y" then copy and paste all those records 83 times (for 83 employees) and then start using filters to find the records that you need to change.

Its kind of hard to say for sure though without knowing exactly what the source data looks like, so it is just a thought.

[RyFlyBones]

Yea I thought of that too, maybe I can set it to assume yes and only input the no's

[RyFlyBones]

To make another search function but this time you input the name and it displays all the training done by that name, is it the same as the search box for searching by training?

[mick0005]

What we could do I suppose is to add another column in the table (say for example called "Subject Complete") and that is where you would indicate if a subject was completed or not (keep in mind the database still needs to be at LESSON level, so you'd still have duplicate subjects for each employee (since you need to tie the subject to the lesson still).

Then we'd need to set up some additional reference table which created the parent child relationship between Subjects and Lessons so that then we could have a formula that looked at the subject and looked at the value in the subject completion column (Y or N) and if Y, then it would look at the reference table and mark all lessons that belong to that subject as "Y".

Is this the kind of thing you are thinking about?

Why not just set up the Y/N field for subject, and then the formula in lesson is if Subject Completion = Y, then Y for lesson.

Please Login or Register  to view this content.

[RyFlyBones]

I'm not so sure how to visualize this, where would the column go and how would I duplicate the subjects? Can you add it to the sheet so I can understand where you're coming from? It seems like your second idea is a lot easier, where in the formula would I add the IF statement.

[mick0005]

To make another search function but this time you input the name and it displays all the training done by that name, is it the same as the search box for searching by training?

Yes, the concept would be the same. You need to change the column that it references (this is the 1 at the very end of the formula, 1 where it is referencing the names, 2 would reference Type, and so forth.... Also, you would need fewer criteria (only name) whereas the other one was referencing type, subject, and lesson.

I mocked something up here... look at the difference between the formulas and maybe this will help you to understand how the formula works.Training tracking database - array for training results 2.xlsx

[RyFlyBones]

It makes a lot more sense now. Thank you for all your help. I will mark the thread as solved, if I run into any troubles I may Pm you as a last resort but hopefully it does not come to that. Thank you

[mick0005]

Reply here, I am subscribed to the thread so will get a notification. That way, others will benefit from whatever solutions we come up with, files they can download and view, etc...

Happy to help. Every time you help someone on here you sharpen your own skills and I get to save the solution to some problem into my own collection to reference later when I encounter a similar scenario.

[RyFlyBones]

Hey,
The search function for searching for a specific employee name and it displaying all the completed training (last thing added) is a bit buggy. The "Lessons" column displays 0 when it is complete, is there any way to switch the string '0' to 'All'. Also, if the subject is incomplete 'N' and the lessons too, the subject is still showing up when the name is searched as if it was complete.

[RyFlyBones]

Also, the search function for searching by training is no longer working. Is this the right formula?

=IFERROR(INDEX(tblTrainingRecords, SMALL(IF(COUNTIFS(Display!$B$19, tblTrainingRecords[Type], Display!$B$20, tblTrainingRecords[Course],Display!$B$21,tblTrainingRecords[Lessons]), ROW(tblTrainingRecords)-MIN(ROW(tblTrainingRecords))+1), ROW(A1)),1),"")

[RyFlyBones]

Database Draft.xlsxHere is the sheet attached with the moved around format, maybe it affected the formulas.

[mick0005]

My original assumption was that you were going to create the database with all records for completed trainings. Therefore the formulas I wrote bring in ALL of the records it finds for the given employee or the given training combination.

I needed to add more criteria to only bring in records that ALSO have a "Y" for both the Course Completed field and the Lesson Completed Field. Now it will only bring in the ones that are actually completed.

You had the formula wrong. This is what it should have been:

Please Login or Register  to view this content.

This is what it is now that I added the criteria for "Y" completed of course and lesson:

Please Login or Register  to view this content.

Here is the file fixed up.Training tracking database - array for training results 3.xlsx

[RyFlyBones]

That's exactly what I was thinking and I was trying to adjust the formulas but kept on running into errors but now I see where I was going wrong. I need to study my formulas uses more, well arrays that is. Thank you

[mick0005]

I'm still not that good at arrays. It takes awhile. You just need to keep using them and encountering different applications and experimenting.

Are you now fully up and running?

[RyFlyBones]

I've noticed in "search member by training option" the "completed" cell where it shows a 'Y' or 'N' only shows a 'Y' if the specific Lesson is highlighted in the tbltrainingrecords. So it does not recognize that if the subject is complete all the lessons are complete as well. I guess that is another formula that needs to be adjusted since we added more criteria and variables to the tblTrainingRecords

[RyFlyBones]

Same thing for the 'search by training' it does not recognize that if a subject is complete then it means all the lessons are complete as well so it does not show it as complete unless the lesson is selected in tbltrainingrecords

[RyFlyBones]

Here are screenshots showing it pic 1.png pic 2.png

I think the fix would be for the formulas to check if the subject is complete before the lessons and if the subject is complete to fill all lessons with a Y

[RyFlyBones]

Also for the 'search by training, is there a way to add another criteria (same as we did for the table) where you can search by subject and it will show all those who have completed the subject and then you can search by subject AND lesson as well. I think all it is is shortening the formula.

[RyFlyBones]

I ended up figuring it out somewhat aha. My question is, that I've figured out how to add my "Comments" section to the "Search by Member" and the "Search by Member and Training" areas by midifying the array code but I can't figure out how to add a comments section to the "Search by Training' area.