How to get case sensitive validation from list in a table?

I’ve been using Excel for 10 years but this small task is trumping me.


Situation:
Sheet1 contains a table called “LessonsCompleted”. I need to validate all entries in the column titled “Student” so that they have an exact, case-sensitive match to a lookup list.

The lookup list is on Sheet2 and is in a table called “StudentsMasterList” and stable column called “Name”.

I learned how tot do this with arrays (Ex. $A$2:$A$10) but cannot figure out how to do it while referring to data in a table. What data validation custom formula would you use?

Attached is the sample spreadsheet if you'd prefer to use that.

Thank you!

Sample Workbook.xlsx

Not entirely sure what you want to achieve. if what you want is a drop down menu in the LessonsCompleted table, column "Student", based on the list in the StudentsMasterList table do this:
1. Select the students from the StudentsMasterList, without the heading (in this case just select cells A2:A5)
2. Create Name Range by going to FORMULAS->Define Name
3. Make sure the "refers to:" field has =StudentsMasterList[Name]
4. Name this range in the "Name" field
5. Go to the LessonsCompleted table and select column B (B2:B5). Insert Data Validation, select "List" in the Allow field. Click in the Source field and leave blank. Press f3 and select the name range you created. Click OK and there you have it.

Sinon05 thank you for the instructions. I followed them but unfortunately, this validation method is not case-sensitive. My main challenge is making it case sensitive.

These names end up in reports that are sent to the students. If the teachers enter the names with strange capitalizations by mistake (which happens all the time), then it just looks unprofessional. We're trying to prevent that.

Originally Posted by ChemistB

Smart solution.

Oh boy I made a mistake. It actually doesn't work . Seems to be trouble with the "INDIRECT" part. Any ideas of how to make it work properly?

Originally Posted by ChemistB

This should work

=AND(ISNUMBER(MATCH(B2,Sheet2!$A:$A,0)), EXACT(B2, PROPER(B2)))

No need to modify iterations or any other options.
Does that work for you? If not, maybe post an updated workbook.

WOW. ChemistB as far as I can see this works perfectly. I don't even completely understand what you've created but you're a genius. I hope other people will find this too! It's a tricky and probably common issue.