Excel validations

[Sheik Suffiyan]

Hi.
i've a excel sheet where i need to perform validations.
ex: i've a column by name Item Number.
validations to be performed on column is:
-> if user enters alphabets, it should display message Item Number should be numeric only.
-> it should not be blank
-> user should enter value only between 1-999. if he/she enters exceeds the provided range, error message should be displayed.

Please help..

Thanks

 

[Trevor_S]

Select the cells that you want validation to apply to, then select Data Validation from the Data menu.
In the Settings tab of the window that appears, pick:

• "Allow:" Whole number
• "Data:" between
• "Minimum:" 1
• "Maximum:" 999

Untick "Ignore blank"

On the Error Alert tab, pick "Style:" Stop, and type an appropriate error message. Remember the same message will be displayed for all errors.

 

[mumps]

VBA approach:
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. I assumed your Item Number column is column A. Change the Range("A:A") in the first line of the code to match your Item Number column. Close the code window to return to your sheet. Enter an Item number.

Private Sub Worksheet_selectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Dim response As String
    Do
        response = InputBox("Please enter an Item Number.")
        If response = "" Then
            MsgBox "You must enter am Item Number.", vbOKOnly, ""
        ElseIf Not IsNumeric(response) Then
            MsgBox "The value entered must be a number.", vbOKOnly, ""
        ElseIf response < 1 Or response > 999 Then
            MsgBox "You must enter a number between 1 and 999.", vbOKOnly, ""
        Else
            Target = response
            Exit Do
        End If
    Loop
End Sub

 

[Sheik Suffiyan]

Thank you so much for your responses.. it worked for me.. i need another help in validations.EX: in my sheet, I've column by name 'Type of Cost'. this column cells should hold only 'Labour' , 'Material' , 'Other'.if user types anything apart from the above mentioned values. it should display message as "Type of Cost can only be Labour, Material, Other"

 

[Peter_SSs]

Thank you so much for your responses.. it worked for me.. i need another help in validations.EX: in my sheet, I've column by name 'Type of Cost'. this column cells should hold only 'Labour' , 'Material' , 'Other'.if user types anything apart from the above mentioned values. it should display message as "Type of Cost can only be Labour, Material, Other"

Use Excel's standard built-in Data Validation with type List

Data ribbon tab -> Data Validation -> Data validation ... -> Settings tab -> Allow: List -> Source: Type the values separated by commas or point it to a sheet range containing the list -> Error Alert tab -> Error message: "Type of Cost can only be Labour, Material, Other"

 

[Sheik Suffiyan]

Thank you peter. it helped for me... i've another set of validation to be performed in my excel sheet.
I've added a dropdown for the above mentioned column "Type of cost" for which we validated i.e Labour, Material & Other... now the validation is:
suppose if the column(A) is of "Type of Cost". and if user selects say "Labour" in column(A) then Column(B) should become mandatory,
like wise if user selects "Material" in column (A) then column (C) should become mandatory & once column(B) or column(C) becomes mandatory, i should be able to perform validations on the columns(B or C) which ever has become mandate.

Please Help..