Formula Needed!!!

S

SteveR

I need a formula for a wookbook. I want to limit or restrict the valu
of a single cell to any whole number multiple of 16. So far I have
been unable to come up with a workable formula. I would appreciate an
help I can get
 
J

Jason Morin

Although it has its limitations, you could use Data >
Validation > Custom and use the formula:

=AND(MOD(A1,16)=0,A1>0)

HTH
Jason
Atlanta, GA
 
S

SteveR

Jason said:
*Although it has its limitations, you could use Data >
Validation > Custom and use the formula:

=AND(MOD(A1,16)=0,A1>0)

HTH
Jason
Atlanta, GA
-----Original Message-----

I need a formula for a wookbook. I want to limit or restrict the value
of a single cell to any whole number multiple of 16. So far I have
been unable to come up with a workable formula. I would appreciate any
help I can get!


------------------------------------------------
[/url]
~~View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
Click to expand...
 
S

SteveR

I am having alittle trouble using this site and I apologize if I create
a double post. Thanks for the formula. I will try it tomorrow when
get to work. If any one else out there has a suggestion for me, I a
open to all
 
D

Dave Peterson

Maybe you could allow your user to type any non-negative(???) number in the cell
and you could have an event macro that would make sure it was rounded up to the
nearest multiple of 16.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
If IsNumeric(Target.Value) _
And IsEmpty(Target) = False _
And Target.Value >= 0 Then
Target.Value = Application.Ceiling(Target.Value, 16)
Else
Application.Undo
MsgBox "Please enter a number >= 0"
End If

errHandler:
Application.EnableEvents = True

End Sub

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this code in.

Change A1 to whatever range address you want.
 
K

Ken Wright

I believe Jason's method is how most would tackle it, but as per Jason's caveat,
you need to be aware that it is easily overcome by simply pasting data into the
cell.
 
Top