Data Validation - the user keys in a validated value

[PB7]

Hello,

I would like to use Excel data validation. The spreadsheet user needs to be able to key in a value, but the value must be validated against a list. I have tried both data validation using list, and data validation using custom (formula) and can not get this situation to work.

So, when using data validation with a list, the user is forced to pick from a drop-down list. The user can not manually enter a value on the drop down list.

The drop down list here (of good values) is quite long, and time-consuming to scroll through, for a good value. Thus, I would like the option of being able to key in the value too.

I'm thinking I may need data validation custom, but I can't figure out the needed formula, with the formula working with some range of acceptable values.

Can anyone help me? Many thanks.

 

[cjcobra]

I would put a cell next to the one entered and use a LOOKUP to see if there number is in the list. If it is, then, "Number Found", if not "Number not Found")

=IF(ISERROR(VLOOKUP(A1,$B$2:$B$50,1,0)),"Number not found","Number Found")

 

[PB7]

Thanks CJCobra. Question: will this IF statement generate a warning message, similar to the way the data validation will generate a warning message?

 

[T. Valko]

Thanks CJCobra. Question: will this IF statement generate a warning message, similar to the way the data validation will generate a warning message?

Let's assume the user is to enter some value in cell A1.

You have the list of valid entries in the range Z1:Z100.

Setup the validation:

Allow: Custom
Formula: =COUNTIF(Z1:Z100,A1)

 

[PB7]

Many thanks Biff! I tried many functions using the data validation custom approach, with no luck. This Countif solution works great!

 

[T. Valko]

Many thanks Biff! I tried many functions using the data validation custom approach, with no luck. This Countif solution works great!

You're welcome. Thanks for the feedback!