On Apr 12, 10:40 am, "Pete_UK" wrote:
One way is to use Data validation. Select B1, then Data | Validation.
In the Settings tab select List from the drop down, then highlight the
parts of column A where your data is (eg A1:A20). You can specify an
Input message if you want (eg "Please enter a number") by clicking the
Input Message tab, and on the Error Alert tab you can select Type (eg
STOP) with a message "Value not in list".

Another way is to allow the User to enter data in B1, and put this
formula in C1:

=IF(ISNA(VLOOKUP(B1,A1:A20,1,0)),"Not in list","Value is in the list")

Hope this helps.

Pete

On Apr 12, 2:55 pm, "alex" wrote:



I'm trying to analyze a list in Excel but I need some help.

I have list of values in column A (let's say, e.g., the numbers 1
through 100).

I want to compare a single value against the list by creating a box
where the user types a value and Excel checks that value against the
list. If the value is not on the list, a msg box displays such; if
the value is on the list, a msg box displays such and adds the value
to the next column.

E.g.:
A user opens Excel...
In column A are the values 1 to 100.
Somewhere to the right of column A is a txt box or other control where
the user can type a value.
If, the user types '231' a msg box displays 'value not on the list.'
If, the user types '44' a msg box displays 'value is on the list,'
then inserts the number 44 in cell B1.

Any thoughts? Thanks for any help.

alex- Hide quoted text -

- Show quoted text -

Thanks for the help Pete; I'll give it a try.

alex