Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Multiple Excel validations - Single column

Posted on 2006-11-16
Medium Priority
422 Views
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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 47

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
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 47

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Introduction to Processes
Suggested Courses
Course of the Month7 days, 1 hour left to enroll