Ensuring input is rounded up to nearest 10
 

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

Ensuring input is rounded up to nearest 10
 

=CEILING(A1,10)
--
David Biddulph

"RayC" wrote in message
...
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

Ensuring input is rounded up to nearest 10
 

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.

--
Ray


"David Biddulph" wrote:

=CEILING(A1,10)
--
David Biddulph

"RayC" wrote in message
...
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

Ensuring input is rounded up to nearest 10
 

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



"RayC" wrote in message
...
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.

--
Ray


"David Biddulph" wrote:

=CEILING(A1,10)
--
David Biddulph

"RayC" wrote in message
...
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

Ensuring input is rounded up to nearest 10
 

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 procedu

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

On Nov 23, 11:51 am, RayC wrote:
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

Ensuring input is rounded up to nearest 10
 

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


"vezerid" wrote:

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 procedu

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

On Nov 23, 11:51 am, RayC wrote:
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

Ensuring input is rounded up to nearest 10
 

Ray,
thanks for the feedback.

Regards

On Nov 23, 9:14 pm, RayC wrote:
Thanks Kostis, the MOD part works great, I don't need the complex VBA bit for
my simple application. Very much appreciated.
--
Ray

"vezerid" wrote:
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 procedu

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

On Nov 23, 11:51 am, RayC wrote:
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