Prev: prevent user from deleting a tab prevent running macro from menu
Next: list box controls - text property
From: Shawn on 25 Feb 2010 13:49
Is there a way to have the cells validate to quarter units? Specifically, I
only want users to be able to enter .00; .25; .50; or .75 numbers.
--
Thanks
Shawn
From: OssieMac on 25 Feb 2010 14:48
Hi Shawn,

Not sure of the interpretation of your question. Do you mean that all values
must be less than one be in quarter increments or that the values can include
greater than one but must be in quarter increments.

Use Data validation. Select the range to be validated and enter the formula
as if it applies to the first cell of the selected range. (Excel looks after
applying it correctly to the remaining cells in the selection.)

If the first option above then the following in Data Validation formula.
=AND(MOD(A1,0.25)=0,A1<1)

If the second option then the following Data Validation formula.
=MOD(A1,0.25)=0

--
Regards,

OssieMac


"Shawn" wrote:

> Is there a way to have the cells validate to quarter units? Specifically, I
> only want users to be able to enter .00; .25; .50; or .75 numbers.
> --
> Thanks
> Shawn
From: Ron Rosenfeld on 25 Feb 2010 15:06
On Thu, 25 Feb 2010 10:49:01 -0800, Shawn <Shawn(a)discussions.microsoft.com>
wrote:

>Is there a way to have the cells validate to quarter units? Specifically, I
>only want users to be able to enter .00; .25; .50; or .75 numbers.

Data/Validation/Settings

Custom
Formula: =MOD(A1,0.25)=0

--ron
From: Shawn on 25 Feb 2010 15:07
The cells can be less than or greater than 1, but would never be greater than
24 or less than 0. This is on a time sheet and we caputre time to the
quarter unit. So a formula that would validate greater than 0, less than
24.00001 and to the quarter unit would be idea.
--
Thanks
Shawn


"OssieMac" wrote:

> Hi Shawn,
>
> Not sure of the interpretation of your question. Do you mean that all values
> must be less than one be in quarter increments or that the values can include
> greater than one but must be in quarter increments.
>
> Use Data validation. Select the range to be validated and enter the formula
> as if it applies to the first cell of the selected range. (Excel looks after
> applying it correctly to the remaining cells in the selection.)
>
> If the first option above then the following in Data Validation formula.
> =AND(MOD(A1,0.25)=0,A1<1)
>
> If the second option then the following Data Validation formula.
> =MOD(A1,0.25)=0
>
> --
> Regards,
>
> OssieMac
>
>
> "Shawn" wrote:
>
> > Is there a way to have the cells validate to quarter units? Specifically, I
> > only want users to be able to enter .00; .25; .50; or .75 numbers.
> > --
> > Thanks
> > Shawn
From: Shawn on 25 Feb 2010 15:51
I got the following to do just what I wanted (kicks out less than 0, more
than 24, and anything that isn't on a quarter unit).

=IF(MOD(L1,0.25)<>0,FALSE,IF(L1<0,FALSE,IF(L1>24,FALSE,TRUE)))

--
Thanks
Shawn


"Ron Rosenfeld" wrote:

> On Thu, 25 Feb 2010 10:49:01 -0800, Shawn <Shawn(a)discussions.microsoft.com>
> wrote:
>
> >Is there a way to have the cells validate to quarter units? Specifically, I
> >only want users to be able to enter .00; .25; .50; or .75 numbers.
>
> Data/Validation/Settings
>
> Custom
> Formula: =MOD(A1,0.25)=0
>
> --ron
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: prevent user from deleting a tab prevent running macro from menu
Next: list box controls - text property