Help with Validation

Hi Luigi,

You can place a validation on this. For instance if you wanted cell B2 to =2 and cell B3 to =3 and so on you would select cell B2 then Data>Validation>Settings tab select underneath "allow" whole number (this I assume) underneath "Data" equal to underneath "Value" =B1+1 click "OK" then if you drag down this validation as far as you need it. Now if you type in Cell B1 the number 1 and cell B2 the number 2 and so on. The other cool thing about validation is the other two tabs you can place a message such as "I said "assending order" do I need to use smaller words?" make sure you check out the error alert if you use stop, warning, or info it allows users different keystrokes, it doesn't affect you in this case however.

Thanks
Ken

Re: Ken is not working ; - (

Ken,

The warning window is showing up regarless everytime a value is entered even if it's in assending order.

Luigi

Try using this code in the Worksheet - it will check above and below the
entry to ensure it conforms with the required values.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With UsedRange.Cells
Dim n As Integer
Dim r As Integer
n = Target.Column
r = Target.Row
For Each c In Columns(n).Cells
If c.Value <> 0 Then
If c.Row < r Then
If Target.Value <> 0 And Target.Value <> "" And Target.Value < c.Value Then
MsgBox "Values must be in ascending order"
Target.Value = 0
End If
ElseIf c.Row > r Then
If Target.Value <> 0 And Target.Value <> "" And Target.Value > c.Value Then
MsgBox "Values must be in ascending order"
Target.Value = 0
End If
Else
End If
End If
Next c
End With
End Sub

To use the code:
- Right click on the Sheet tab
- Select "View Code" from the menu that appears
- Paste the code in the code window that appears

Any help?

Regards

Robb

Custom data validation :-

For cell A1 :-
=A1<A2<p>For the rest of column A :-
=And(A2>A1,A2<A3)

Look in the comments box .....

The posting got screwed up. Look in the comments box below.

Rob it doesn't work (let me explain better...)

This spread sheets are basically inventory cards (several tabs). Each card represents a product by a number like "03-8577-44" and or some of them are like OFD-123 and so on. Some tabs have 5 cards and other up to twelve cards. These are the cell numbers for a tab that has twelve card; the first (card) product number is on "B76" followed by "B152", "B227", "B302", "B377", "B452", "B527", "B602", "B677", "B752", "B827", and "B902". The only value that needs to be in ascending order are the above mentioned cells. What do you think.... Should I be using a code or a validation? I am very new at this as you can probraly tell. I really appreciate you all time effort in helping me.

Thank you : - ) Luigi Try using this code in the Worksheet - it will check above and below the

: I have this big spread sheet that requires numbers to be entered in assending order. What I want is a validation for example; That if cell B152 is lesser than B76 a windows pops to warn and prevent the operator from entering a non assending ordered value.

Re: Rob read this one I forgot to add some information

Thank you : - ) : Luigi : Try using this code in the Worksheet - it will check above and below the : entry to ensure it conforms with the required values. : Private Sub Worksheet_Change(ByVal Target As Excel.Range) : With UsedRange.Cells : Dim n As Integer : Dim r As Integer : n = Target.Column : r = Target.Row : For Each c In Columns(n).Cells : If c.Value <> 0 Then : If c.Row < r Then : If Target.Value <> 0 And Target.Value <> "" And Target.Value < c.Value Then : MsgBox "Values must be in ascending order" : Target.Value = 0 : End If : ElseIf c.Row > r Then : If Target.Value <> 0 And Target.Value <> "" And Target.Value > c.Value Then : MsgBox "Values must be in ascending order" : Target.Value = 0 : End If : Else : End If : End If : Next c : End With : End Sub : To use the code

John I did what you suggested, but the validation window is displaying regardless if it's in ascending order or not. What am I doing wrong?

Thank you.

Assuming that column B is the target column and the first entry starts in B1.

Select the range of interest from B2 on (Yes, it's from B2 on).
Activate Data|Validation.
Select "Custom" for Allow.
Enter as formula:

=AND(LEN(B1)>0,B2>B1)

Note. No $-signs involved in the formula.

Click OK.

Aladin

===== I have this big spread sheet that requires numbers to be entered in assending order. What I want is a validation for example; That if cell B152 is lesser than B76 a windows pops to warn and prevent the operator from entering a non assending ordered value.

Re: Aladin or can someone tell me why the window is displaying regardless if it's on ascending order.

I've done what you instructed me, but the window pops up even when the number is entered in assending order. Do you want me to e-mail you a copy of this spread sheet? Again I thank you for your help.

Thank you. Assuming that column B is the target column and the first entry starts in B1. Select the range of interest from B2 on (Yes, it's from B2 on). Activate Data|Validation. Select "Custom" for Allow. Enter as formula: =AND(LEN(B1)>0,B2>B1) Note. No $-signs involved in the formula. Click OK. Aladin ===== : I have this big spread sheet that requires numbers to be entered in assending order. What I want is a validation for example; That if cell B152 is lesser than B76 a windows pops to warn and prevent the operator from entering a non assending ordered value.

Re: Aladin or can someone tell me why the window is displaying regardless if it's on ascending order.

> I've done what you instructed me, but the window pops up even when the number is entered in assending order.

Hmm...

> Do you want me to e-mail you a copy of this spread sheet?

Yep.