I have received a number of emails from coaches that are concerned about using a drop down box with all their athlete’s names available to choose from – some of these concerns are athlete’s picking the wrong names by mistake or simply doing it on purpose!
To avoid this, you can simply create an ID table and assign each athlete their own unique login ID, then using VLOOKUP you can create a column that matches the ID to the name, and displays both on a responses spreadsheet.
Firstly, change the first question in your questionnaire to a short answer comment box and name it Athlete ID
This is how it will look for an athlete now.
Then create a separate table to the left of your data with all your athlete’s names and their ID’s
Then using a simple VLOOKUP formula, we can match the ID in B2 against the ID and Name data table and place it in C2. The formula below selects B2 and looks in the cell range R2:S5. In this cell range column 1 shows the athlete ID and column 2 shows the athlete name. Since we want the athlete name we will match B2 with the 2nd column in the cell range R2:S5 hence the formula is as follows.
=VLOOKUP(B2, R2:S5, 2)
Simple but effective!