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
5
Medium Priority
?
422 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
Comment
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
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
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

by:MarkDozier
ID: 17962682
what about b2 - b202?
0
 
LVL 4

Expert Comment

by:hclgroup
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

by:MarkDozier
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

by:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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

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

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

Join & Ask a Question