Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |