Data Validation

M

mteepee

Hello Forum,

up to now, I am working for a very long time on solving followin
problem:

I would like to insert in one cell a whole number between let's sa
1-596. There is a list with all numbers, which are already in the exce
sheet, and should not be allowed to enter to the cell...it should b
profed, if the number i want to enter from 1 to 596 is allready in use
if so, there should come up a alert message. Is this possibe, probabl
with a vlookup in data validation? What should i precice do?

Thank you very much in advance, greetz

Mteepe
 
A

Ardus Petus

Say your data list is in A1:A500
Say your data entry cell is B1:

Data>Validation Customized
Formula:
=ISERROR(MATCH(B1,A1:A500,0))

HTH
 
M

mteepee

Hi Ardus,

thank you very much for your quick response. It works with the formula
=ISERROR(MATCH(C1;$A$1:$A$599;0)) in the data validation.

But one problem occured. The first check is not working anymore. The
first check is, if there is one cell, let's say column b is filled with
a "1" and if so, it should be possible to fill in cell c with the data
validation you told me.

Alltogher:
1. check: is there any nummer in range B1:B599?
2. check: is there any number i want to fill in column C in range
a1:a599?

If 1. is yes, goto 2., if 2. is yes: let me fill in the number I
want....

Probably you can help on the again. Can I send you my Excel Sheet, if
it is more easy to help? Thank you so much and best regards,

mteepee
 
Top