Forum Discussion
autofill date a training was completed by item/person
Hello, how are you doing?
I have been trying to do something in excel for a few days and I have not figured out a formula that works well.
On sheet 1, column A, i have a big list of training items. On the same row for each item in this column, i have USER NAME, TRAINING NAME, COMPLETION DATE.
In this sheet 1, i have the same USER NAME repeated several times, as one person may have completed more than one training.
On sheet 2, column A, i have USER NAME (this time without duplicates) followed by TRAINING ITEM/Completion date.
I want to come up with a formula where excel goes through sheet 1, and autofill the date which the training was completed based on the training item for each person on the list.
i have tried IF formulas without success (SPILL error).
Can you recommend me a way for me to do this? A function i could look up and learn?
This problem is killing me.
I have attached a picture for an example.
Thank you very much for your help!
14 Replies
- mathetes, thank you very much for your time and effort.
I believe that this solution will work. I will be trying it in the next couple of hours and I will let your know. I want to be able to fully understand the formula so I can use it well.
Also, the "initial" part is a different training, like phase 1 and phase 2. In my example, no one completed the "initial" part of the training.
Once again, thank you. I truly appreciate that you spared some time to help me out.This is the key formula
=FILTER(Report!$C$2:$C$5,(Report!$A$2:$A$5=Sheet2!C$2)*(Report!$B$2:$B$5=Sheet2!$A3))
What this does is filter (from the table in the Report tab) the date column where the course equals the course named in row 2, the name equals the name in column A, and just display the date (which is in column C of the "Report" tab. This works in this case--and the multiple criteria can be used--because there's by definition only one date that is in the intersection of all those criteria. Otherwise, FILTER delivers a "dynamic array"--often multiple rows.
Since "Initial" is also a course, now that you've clarified that, you can simply copy the formula from one of the adjacent cells into that column.
Here's a video from which you can learn about the full power of FILTER. It's a relatively new function.
