Solved

Multiple Excel validations - Single column

Posted on 2006-11-16
5
415 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
  • 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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

777 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