[data--->validate]

[Karol_tom]

Hello,

In my work, when I adding new entry to some big excel documment, I must
check if
this number doesn't already exist.
So, I use CTRL+F and then check...but I know this is stupid and wasting my
time
I know that many people ask about it and there is reason - data validating..
So I try:
http://yfrog.com/17cz01g
but it doesn't work...
maybe the reason is that there IS some numbers that duplicates...
I can't delete this rows, but as I say - new entry which I add, can't exist
later.

 

[Ms-Exl-Learner]

Hello,

In my work, when I adding new entry to some big excel documment, I must
check if
this number doesn't already exist.
So, I use CTRL+F and then check...but I know this is stupid and wasting my
time
I know that many people ask about it and there is reason - data validating..
So I try:http://yfrog.com/17cz01g
but it doesn't work...
maybe the reason is that there IS some numbers that duplicates...
I can't delete this rows, but as I say - new entry which I add, can't exist
later.

Assume that you are entering data on Column A.
Place the Cursor in A1 cell and hit Cntrl+Spacebar which will select
the whole Column A and the A1 will be the Active cell (Active Cell
will have a white Background after selection also).

Select Data>>Validation>>Settings>>Validation Criteria>>Select CUSTOM
from the Drop Down>>Under the Formula copy and paste the below
formula.

=COUNTIF(A:A,A1)=1

Move on to the ERROR ALERT Tab on the same Data Validation Dialog box
and follow the instructions.

Error Alert>>Make sure whether the “Show Error Alert After Invalid
Data Is Entered” is having a Tick Mark.

Error Alert>>Style>>Select the STOP from the Drop Down List.

Error Alert>>Title>>Under the Title copy and paste the below Text.
Data Already Found

Error Alert>>Error Message>>Under the Error Message copy and paste the
below Text.
The Data You Are Entering Is Already Present In This Column.

Give Ok..

Now input the value in column-A and type some duplicate values and see
whether you can able enter duplicate values.

(The reason for requesting you to place the Cursor in A1 cell while
selecting the whole Column A is because, the above formula is
referring the cell A1 so the Active cell should be in A1 cell while
applying the validation. If suppose if you are located your cursor
(Active cell) in A3 cell and selected the whole column (Column A) and
applying the above formula will not result valid duplication checking)

Hope it’s clear to you!

 

[Karol_tom]

Select Data>>Validation>>Settings>>Validation Criteria>>Select CUSTOM
I nedd to do this in column B, I should simply change A into B in above
formula ?

 

[Ms-Exl-Learner]

Yes, change the cell references of the formula to B like the below:-
=COUNTIF(B:B,B1)=1

Now the formula is referring to B1 as active cell so place your cursor
in B1 and hit Cntrl+Spacebar to select the whole column and follow the
procedures.

 

[Ms-Exl-Learner]

Yes, change the cell and range reference to B like the below:-
=COUNTIF(B:B,B1)=1

But now the formula is referring to B1 cell, so B1 is the active cell,
place your cursor in B1 cell and hit Cntrl+Spacebar to select the
whole B Column and B1 will have a white background after selection
also. Follow the remaining procedures as mentioned in my previous
post.

 

[Karol_tom]

I have another question - is there any possibillity to add
restricted values which I can NOT entry - my idea is formula using many if,
like:
if(cell="restricted_value1";if(cell="restricted value"

and so on

but is there any other possibillity ?

 

[Ms-Exl-Learner]

Yes, we can. how much will be the restricted values? and if possible
give me some data about the restricted values.

 

[Karol_tom]

okay - in cells located in this column I can enter any value (any lenght,
numbers or text or even their combination).
There're some numbers which I shouldn't enter here (and also their
combination).
So only what I should check is if new entry doesn't contain this numbers

 

[Ms-Exl-Learner]

Use your formula like the below in your Validation.

=AND(COUNTIF(B:B,B1)=1,COUNTIF(B:B,2500)=0,COUNTIF(B:B,
2000)=0,COUNTIF(B:B,"YES")=0)

In the above formula the users cant able to enter duplicate values and
the number 2500, 2000 and the word "YES" in Column B. Note that if
the countif criteria is Text Character then mark it in Double Quotes.

 

[Karol_tom]

=AND(COUNTIF(B:B,B1)=1,COUNTIF(B:B,2500)=0,COUNTIF(B:B,

2000)=0,COUNTIF(B:B,"YES")=0)

In the above formula the users cant able to enter duplicate values and
the number 2500, 2000 and the word "YES" in Column B. Note that if
the countif criteria is Text Character then mark it in Double Quotes.

When I writing this formula, I only get error message.
Should I use ; rather than , ?
(countif(B:B;"value1") instead of countif(B:B,"value1") )

 

[Ms-Exl-Learner]

Have you copy and pasted the below formula in validation. B1 is the
Active Cell.
=AND(COUNTIF(B:B,B1)=1,COUNTIF(B:B,2500)=0,COUNTIF(B:B,
2000)=0,COUNTIF(B:B,"YES")=0)

After assigning validation check whether you can able to type 2000 or
2500 or the work Yes in B column?