Data Validation Using COUNTIF

Question

I am trying to use the data validation function in Excel to see if a user-entered ID exists in our system. The user will enter the ID number into a table (formatted as a named table), then check in another table (formatted as another named table) if that ID number exists. If the ID exists, no error is raised. If the ID does not exist, it will alert the user. The purpose of this is to prevent user error when entering in the ID numbers.

In the data validation popup, I've selected Custom and then entered in the following formula:

=COUNTIF(PO_BEN, A3) > 0

Where PO_BEN is the ID number found in the validation table and A3 is the user-entered ID number. PO_BEN is a named range I've made using the name box after selecting that entire column from the table.

Data Validation Popup

No matter what I type into the user entry cell (A3), no error message will pop up. I can intentionally enter in IDs that do not exist and no error will be raised. When I type the code above in a random cell, I get the expected results.

Below I've also included the web page I got the formula from.

https://exceljet.net/formula/data-validation-unique-values-only

What is the format of your IDs? Can you give us a sample ID that doesn't work? — BigBen
When you click on "Error Alert" in the data validation pop-up, what setting is selected? — Cyril
IDs are all formatted as text in both the user entry table and the validation table. Typically, the IDs are a 7-digit number followed by a single letter. For example, 1900123Q. An ID that shouldn't work (i.e., it isn't in the validation table) is "junk." — user10918650
Also, your data validation formula is supposed to be where the data exists, not the formula checking true/false. The example you've provided from Excel Jet is just a true/false case, which is in a cell separate from the entered data. — Cyril
No repro with the current information you've provided. Maybe a screenshot of your setup would help? — BigBen

Unknown Unknown · Accepted Answer · 2019-10-28T19:05:10

I've found a workaround that involves using a helper column. I was able to get the same formulas to work in cells but for whatever reason I couldn't in the data validation menu. Not the most elegant, but it works.

In a separate column:

=AND(COUNTIF(PO_BEN,USER_BEN)>0, USER_BEN=SUBSTITUTE(USER_BEN," ",""))

All the names used in the formulas are named ranges. PO_BEN is the range of BEN numbers from the validation table. USER_BEN is the range of BEN numbers inputted by the user.

The additional validation in this formula looks for any spaces the user might have entered. If the user enters a space anywhere in the BEN number, it will also flag the entry.

I'm still convinced there is a cleaner solution that doesn't include a helper column so I'll keep working on it.

Data Validation Using COUNTIF

1 Answers