MarkDozier
asked on
Multiple Excel validations - Single column
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
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
ASKER
what about b2 - b202?
Just select b2-b202
then go to Data --> Validation
and it will be applied to all the cells you selected
then go to Data --> Validation
and it will be applied to all the cells you selected
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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