Solved

Multiple Excel validations - Single column

Posted on 2006-11-16
5
418 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 250 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
This is an explanation of a simple data model to help parse a JSON feed
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

738 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