Ensuring input is rounded up to nearest 10

[RayC]

I am creating a form for hiring cutlery for my local village hall (a
charity). I need to ensure that hirers only order cutlery in multiples of 10.
How can I validate their input so that if they enter eg 33 it is
automatically rounded up to 40?
Thanks

Ray

 

[David Biddulph]

=CEILING(A1,10)

 

[RayC]

Thanks, but how do I apply it to the same cell to avoid a circular reference?
If the user tried to enter 33 I want that cell to show the 40.

 

[MartinW]

Hi Ray,

Bit of a compromise on what you are asking for, but you could
set Data Validation to custom with a formula like this,
=OR(A1=10,A1=20,A1=30,A1=40,A1=50)
and then set the error alert message to say
"Input must be in multiples of 10"

HTH
Martin

 

[vezerid]

To ensure, in Data Validation, that the number is a multiple of 10: If
you are working with, say, cell A1, in DV choose Custom and this
formula:

=MOD(A1,10)=0

Now, to automatically round up the entered number to the nearest
multiple of 10 you need a VBA event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ' Change A1 to suit
If Target.Value Mod 10 <> 0 Then
ceil = Application.WorksheetFunction.Ceiling(Target.Value, 10)
msg = "You should enter a number in multiple of 10" & vbCrLf
msg = msg & "Automatically increasing your amount to " & ceil
MsgBox msg
Target.Value = ceil
End If
End If
End Sub

Right-click the sheet tab. Choose View Code... The VBA IDE comes up.
Paste the above code to the window, after changing $A$1 to whatever
cell contains the input.

Does this help?
Kostis Vezerides

 

[RayC]

Thanks Kostis, the MOD part works great, I don't need the complex VBA bit for
my simple application. Very much appreciated.

 

[vezerid]

Ray,
thanks for the feedback.

Regards

Thanks Kostis, the MOD part works great, I don't need the complex VBA bit for
my simple application. Very much appreciated.