data validation
 

is there anyway to have 2 conditions in data validation

right now i have a count formula that alerts with error if a duplicate
number is put in cells A1:A10

I would like to add another condition that won't allow a user to input
a number out of sequence

example ......cellsA1:A10 are the range

a user can put the number (1) into any of the cells in the range a1:a10
and can put number (2) anywhere and so on. But they can't put the
number 3 until the number 2 has been put in. They can't put 6 until 5
is input in the range.

Thanks for any solutions.


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=377184

data validation
 

You have to use macros

Paste the below code by right click sheet-view code

Below code restricts range a1:a10 to be in sequence. You can expand the
range by changing range values in the code.

code will only work on the condition that you don't delete numbers in
the sequence, however you can resolve this problem.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim adr As Variant
adr = Target.Address
On Error Resume Next
If Range(adr).Value = "" Then
End
End If
If Err.Description < "" Then
Err.Clear
End If
If Target.Column = "1" And Target.Row <= 10 Then
On Error Resume Next
t = WorksheetFunction.Large(Range("a1:a10"), 2)
If Err.Description < "" Then
Err.Clear
End If
If Range(adr).Value = t + 1 Then
End
Else
MsgBox "out of sequence"

Range(adr).Value = ""
End If
End If
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=377184