two space rounding
 

I have a spreadsheet that multiple users use. It allows them to capture
totalt time worked in a range of cells. They can enter anything from .25 to
24.00. The cells are formatted to round to two decimal places. I want to
restrict what can be entered into the cell to a value equal to or greater
than .25 and less than or equal to 24.00. I have accomplished this with the
validation feature.

Here is my problem. If they type in a decimal entry, I want them to be
restricted to .25, .50 or .75 (can be used with whole numbers, too, ie. 4.25
or 7.75).

How can I accomplish this easily?

Sometimes people will type in for example 8.24. The cell rounds up to 8.25
so it looks correct but the ultimate tally is off by .01


--
Thanks
Shawn

two space rounding
 

One non-vba solution would be to use a data validation list of
"0.25,0.5,0.75,1 etc.".

This will also allow you to enter a custom error message that will
appear should the user enter 2.24 rather than 2.25, say.

If you need a VBA solution then post back. Personally, I would only use
VBA however if you are using VBA anyway - IMHO there's no point
introducing the (minor) headache of ensuring macros are enabled etc. for
multiple users.

HTH,
Gareth

Shawn wrote:
I have a spreadsheet that multiple users use. It allows them to capture
totalt time worked in a range of cells. They can enter anything from .25 to
24.00. The cells are formatted to round to two decimal places. I want to
restrict what can be entered into the cell to a value equal to or greater
than .25 and less than or equal to 24.00. I have accomplished this with the
validation feature.

Here is my problem. If they type in a decimal entry, I want them to be
restricted to .25, .50 or .75 (can be used with whole numbers, too, ie. 4.25
or 7.75).

How can I accomplish this easily?

Sometimes people will type in for example 8.24. The cell rounds up to 8.25
so it looks correct but the ultimate tally is off by .01

two space rounding
 

that would be a long string wouldn't it? 24 * 4 = 76 enteries in the
valadation per cell. Wouldn't that weigh the program down a lot?
--
Thanks
Shawn


"Gareth" wrote:

One non-vba solution would be to use a data validation list of
"0.25,0.5,0.75,1 etc.".

This will also allow you to enter a custom error message that will
appear should the user enter 2.24 rather than 2.25, say.

If you need a VBA solution then post back. Personally, I would only use
VBA however if you are using VBA anyway - IMHO there's no point
introducing the (minor) headache of ensuring macros are enabled etc. for
multiple users.

HTH,
Gareth

Shawn wrote:
I have a spreadsheet that multiple users use. It allows them to capture
totalt time worked in a range of cells. They can enter anything from .25 to
24.00. The cells are formatted to round to two decimal places. I want to
restrict what can be entered into the cell to a value equal to or greater
than .25 and less than or equal to 24.00. I have accomplished this with the
validation feature.

Here is my problem. If they type in a decimal entry, I want them to be
restricted to .25, .50 or .75 (can be used with whole numbers, too, ie. 4.25
or 7.75).

How can I accomplish this easily?

Sometimes people will type in for example 8.24. The cell rounds up to 8.25
so it looks correct but the ultimate tally is off by .01

two space rounding
 

I agree: it would be fairly long, 439 char by my reckoning (see below
code to make the string should you want it). But I shouldn't *imagine*
(I stress that because I haven't tested it) it would be that bad - Excel
wouldn't repeat it for every cell (it's smarter than that).

Oh... bunnies. I just tried pasting the string into the data validation
list and it's too long - I think 255 char is the limit.

Alternatives:

(1) Write the data validation source list onto the worksheet you're
using (in a hidden column). That *does* work for all 96 rows - I just
tried it....

(2) Revert to VBA and trap Worksheet_Change events to correct / abort
user data entry.

(3) Allow the incorrect entries and correct them in your formulae when
you total up.

HTH,

Gareth

----------------------------------
Sub temp()
Dim i As Single
Dim myString As String
Do While i < 24
myString = myString & i & ","
i = i + 0.25
Loop
myString = Left$(myString, Len(myString) - 1)
ActiveCell = myString
End Sub



Shawn wrote:
that would be a long string wouldn't it? 24 * 4 = 76 enteries in the
valadation per cell. Wouldn't that weigh the program down a lot?