Data Validation

  • Thread starter Thread starter Soniya
  • Start date Start date
S

Soniya

Hi All,

How can I set a data validation to a cell to accept
either multiples of 10 or 15 ?
any help..

TIA

Soniya
 
Hi Soniya

Don't think you can without code -hope I'm wrong.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = "" Then Exit Sub
If Val(Target.Value) Mod 10 = 0 Then
ElseIf Val(Target.Value) Mod 15 = 0 Then
Else
Target.Value = ""
End If
End If
End Sub
 
Soniya said:
Hi All,

How can I set a data validation to a cell to accept
either multiples of 10 or 15 ?
Click to expand...

Hi Soniya,

Choose Data/Validation and select the Custom option from the Allow
dropdown. Then enter the following formula:

=OR(MOD(A1,10)=0,MOD(A1,15)=0)

Be sure to change A1 to the address of the cell you're applying this
validation to.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Soniya said:
Hi All,

How can I set a data validation to a cell to accept
either multiples of 10 or 15 ?
Click to expand...

Hi Soniya,

Choose Data/Validation and select the Custom option from the Allow
dropdown. Then enter the following formula:

=OR(MOD(A1,10)=0,MOD(A1,15)=0)

Be sure to change A1 to the address of the cell you're applying this
validation to.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
In the first cell where you wish to apply data validation (D32 in this
example), in the validation criteria box select "Custom". In the "Formula"
box enter the following and then copy the cell to the range where you want
the same type of validation.
=OR(MOD(D32,10)=0,MOD(D32,15)=0)

--
XL2002
Regards

William
(e-mail address removed)

| Hi All,
|
| How can I set a data validation to a cell to accept
| either multiples of 10 or 15 ?
| any help..
|
| TIA
|
| Soniya
 
Choose Data/Validation and select the Custom option from the Allow
dropdown. Then enter the following formula:

=OR(MOD(A1,10)=0,MOD(A1,15)=0)
Click to expand...

Cool. Thanks Rob. You live and you learn. (You in this case is me :-)
Now why is this suddenly "custom" and not "formula is" like in conditional formatting ?
Strange.

Best wishes Harald
Excel MVP

Followup to newsgroup only please.
 
Harald Staff said:
Now why is this suddenly "custom" and not "formula is" like in conditional
Click to expand...
formatting ?

Hi Harald,

Custom refers to the type of data validation you want to apply. It's one
of the selections in the Allow dropdown, which includes Whole number, List,
Date, etc. Once you've selected Custom from the Allow dropdown, the control
where you enter the formula is labeled "Formula", which is not too much
different from conditional formatting.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Back
Top