Data Validation

[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

 

[Harald Staff]

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

 

[Rob Bovey]

Hi All,

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

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 *

 

[William]

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

 

[Harald Staff]

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)

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.

 

[Rob Bovey]

Now why is this suddenly "custom" and not "formula is" like in conditional

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 *