Data Validation (custom) formula help

**MisfitJr** · 07-27-2023, 01:21 PM

I have an excel sheet where employees enter part numbers in a column. But I'm running into problems with them misspelling the part numbers. So I made a list on a separate sheet on the same workbook of every part number in our system.
The goal is to reject every entry that doesn't have a exact match with my list I made.

Example:

What they entered (on first sheet)

631212SVRT-62

What SHOULDVE been entered

631212SVRT-628

And since this number is on my list of numbers, I would like excel to reject it because the number they entered didn't match any number on my list.
I would prefer to do this on "Data Validation" with a custom formula. I just don't know what formula to use for this.

Thanks for the help

**Greg M** · 07-27-2023, 02:56 PM

Hi there,

Does the attached workbook get you moving in the right direction?

It uses a Defined Name Range (ptrPartNos) which contains all of the allowable Part Nos, and the Data Validation is set as List, =ptrPartNos

Hope this helps.

Regards,

Greg M