I've been wracking my brain all afternoon trying to find an easy, efficient and simple formula for the following;
I have a very large worksheet that is used to keep a record of any/all training done by all employees. We've recently started tracking and reporting on mandatory compliance training. I use salary numbers as my key field. Because all employees engage in more that 1 training session, there are duplicates of their salary numbers. I also have a secondary key field (Learning Code) that is unique to each of the mandatory trainings.
Instead of manually filtering, counting, etc at the end of every month, I'd like to create a dynamic summary that will update every time a record is entered.
I have a separate worksheet that contains all employee data (salary number, unit, functional line, etc). I would the formula to reference the employee data (salary number), and search through all the training records to check what mandatory training has been completed and when. These values are then displayed beside the employees name.
I've tried nesting vlookup with the IF function, to create multiple conditions, but I can't get my forumla to continue searching through the rest of the training records once it finds the first instance of the salary number.
I've been at this all afternoon and actually think that my ideas are getting dumber! Please help!
Should there be a need,l i can attach a sample of the worksheet I've created. But right now, I'm just looking for a function that searches through an array for multiple conditions and then returns referenced values.