Data Validation

gimli

New Member
Joined
Nov 11, 2009
Messages
37
Hello all,

Trying to add multiple data validations for a cell and having hard time getting conditions to work.
Restrictions are numbers between 0 and 100 or just the text value of x. Any suggestions
would be great!

thanks!
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,886
Office Version
365, 2019
Platform
Windows
Assuming your validation is setup in A1, use a Custom validation setting with the following formula:
Code:
=OR(AND(A1>=0,A1<=100),A1="x")
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
441
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim i As Long, str As String
    If Target.Address = "$A$1" Then
        str = Range(Target.Address)
        If IsNumeric(str) Then
            If Val(str) < 0 Or Val(str) > 100 Then GoTo Wrong
        End If
        If Not IsNumeric(str) Then
            If str <> "x" And str <> "X" Then GoTo Wrong
        End If
    End If
    Application.EnableEvents = True
    Exit Sub
Wrong:
    MsgBox "Only allowed 0-100 or x"
    Range(Target.Address) = ""
    Range(Target.Address).Select
    Application.EnableEvents = True
End Sub
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,886
Office Version
365, 2019
Platform
Windows
Sorry, didn't see your post Tracy!
No biggee. Alternative solutions is what makes this site great :)
PS: wait till you see the updated site! Been testing for 2 weeks and I love it!
 

gimli

New Member
Joined
Nov 11, 2009
Messages
37
Thanks for the help...though I responded Friday....maybe didnt hit pst reply.

Anyways,
this code worked perfect.
=OR(AND(A1>=0,A1<=100),A1="x")

Is there a way to add one more validation to that code? I just want to allow integers to be entered..no decimal points.
is that doable ?

Thanks much
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,816
Office Version
365
Platform
Windows
Is there a way to add one more validation to that code? I just want to allow integers to be entered..no decimal points.
is that doable ?
Try
=OR(AND(A1>=0,A1<=100,IFERROR(INT(A1),A1)=A1),A1="x")
 

Forum statistics

Threads
1,078,491
Messages
5,340,683
Members
399,389
Latest member
JayNExcel

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top