Solved

Multiple Excel validations - Single column

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
A short article about a problem I had getting the GPS LocationListener working.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

829 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