I'm struggling with the logic here so any help is appreciated.
My team manages new hires and we have to rate their performance weekly to report back to the new hires respective manager. Our program is 10 weeks long and to consistently rate them on the same metrics week to week, we created a survey that the trainers would fill out once per week per trainee. It ends up in a grid like this:
In this same form, I've created helper columns to convert the star ratings to a numeric value:
Pretty straight forward.
Now, the complex part is all the New Hires go into one form. We have up to 4 new hire classes going on at once and trainers inputting data at the same time. I want to extract the metrics per trainee into a separate sheet per class.
Here's what I've got so far:
So the Trainee column is populated with a formula using Index Match to pull from our Master NH tracking sheet using the Member Number.
Then, I created a row for each week for each New Hire. In the next 6 columns, I want to pull from the specific Numeric Column for the user per week that was input by the trainer.
For example, the Trainer puts in that NH021-1 received a 4 for Understanding Material in Week 1, then a 4 would appear in Column3, Row2 (where the error message is currently)
Here's the formula I currently have:
=INDEX({Status - UM}, AND(MATCH(Trainee1, {Status - Name}, 0), MATCH([Week #]@row, {Status - Week}, 0)))
I tried using AND to Match twice, once for the Name of the Trainee, and again for the week.
I'm familiar with SQL querying so I'm struggling not being able to use WHERE clauses.
Thanks in advance for the help!
