Solved

# Multiple Excel validations - Single column

Posted on 2006-11-16
Medium Priority
425 Views
Last Modified: 2008-01-09
Hi y'all
I have a form in Excel I want ot do validation on. The requirements are
Validate the number is whole such as 1 - if 1.x is enter I also want it to truncate to one and if 1/5 or greater got enter I still want it to truncate to 1.
Validate the value of the column is not a letter(s).

I know how to do each of these separately
for numbers only I use Whole Number but that allows letters to be used.
If I use the COUNTIF it stops letters but allows decimals.

Is it possible to combine the 2 to make a validation that will allow:
Only whole numbers and prevent letters from being entered?

Thank you
0
Question by:MarkDozier
• 2
• 2
5 Comments

LVL 48

Expert Comment

ID: 17961896
Hi Mark,

Assuming the cell you want datavalidation in is A1, in Data Validation, select Custom, and use this formula....

=IF(ISERR(INT(A1)), FALSE, INT(A1)=A1)

This will only allow the Whole Numbers, and prevent *everything* else.

Regards,

Wayne
0

LVL 8

Author Comment

ID: 17962682
what about b2 - b202?
0

LVL 4

Expert Comment

ID: 17965806
Just select b2-b202

then go to Data --> Validation

and it will be applied to all the cells you selected
0

LVL 8

Author Comment

ID: 17968415
Ah fudge. I forgot to post one more requirement.
There can be not duplicate numbers in the ID# column.

EXAMPLE Column
1
1 (This should error and give msg "This is a duplicate number." ( I know how to do the message.
23
24
34
35

HCLGROUP
I can the number validation using "Whole Number" from the validation drop down menu. But I like your code.
0

LVL 48

Accepted Solution

Wayne Taylor (webtubbs) earned 1000 total points
ID: 17969582
Select cells B2:B202, go to Data validation, the use the following formula...

=IF(ISERR(INT(B2)), FALSE, IF(COUNTIF(B:B, B2)>1, FALSE, INT(B2)=B2))

Wayne
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Simple Linear Regression
###### Suggested Courses
Course of the Month9 days, 3 hours left to enroll

#### 621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.