PDA

View Full Version : data validation - number or number / 2



clarksonneo
04-02-2011, 06:09 AM
Hi,

I would like to create a data validation.

In a coulmn, I would like to input number.
The things I input will have 2 forms only.

Form1: just number
Eg, 2000, 54.22,

Form2: number divided by 2
Eg, =2000/2, =50.2/2, = 101/2

How can I create such data validation?

Thanks

Bob Phillips
04-02-2011, 07:30 AM
Maybe this DV formula

=OR(ISNUMBER(A2),AND(ISNUMBER(--LEFT(A2,LEN(A2)-2)),RIGHT(A2,2)="/2"))

clarksonneo
04-02-2011, 10:58 AM
Maybe this DV formula

=OR(ISNUMBER(A2),AND(ISNUMBER(--LEFT(A2,LEN(A2)-2)),RIGHT(A2,2)="/2"))

Hi,

I think it does not work

Firstly, it doesn't work when I use it at data validation.

Secondly, when I input =200/2 in cell A2,
the following formula won't return true
=AND(ISNUMBER(--LEFT(A2,LEN(A2)-2)),RIGHT(A2,2)="/2")

so I guess this part of formula should have problem

thanks

Bob Phillips
04-02-2011, 03:23 PM
If you really add the =, then use

=AND(LEFT(A2,1)="=",ISNUMBER(--MID(A2,2, LEN(A2)-3)),RIGHT(A2,2)="/2")

mikerickson
04-02-2011, 05:20 PM
Form1 and Form2 are the same. Every number is 1/2 of some other number.

Or do you want
Form1=number
Form2=a string that begins with a numeral and ends with "/2"?

clarksonneo
04-02-2011, 05:39 PM
Form1 and Form2 are the same. Every number is 1/2 of some other number.

Or do you want
Form1=number
Form2=a string that begins with a numeral and ends with "/2"?
to: xld (http://www.vbaexpress.com/forum/member.php?u=2139)
That is not work as well.
Let's look at this example.

=200/2 will become 100, so

LEFT(A2,1)="="
false
RIGHT(A2,2)="/2"
false

to:mikerickson (http://www.vbaexpress.com/forum/member.php?u=10706)
"Every number is 1/2 of some other number."
yes, so I don't know how to create such data validation

"Form2=a string that begins with a numeral and ends with "/2"?"
not string.
if I input =200/2, i want the result is 100

thanks

mikerickson
04-02-2011, 05:58 PM
It looks like the problem isn't in data Validation, but that when you type "3/2" into a cell, Excel "sees" March 2 rather than 1.5 or when you type "100/2" into a cell, it sees a string rather than 50.

Perhaps something like

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHalt
With Target
If .Cells.Count <> 1 Then ErrorHalt
Application.EnableEvents = False

If Not (IsNumeric(.Text)) Then
If Val(CStr(.Value)) <> 0 Then
.Value = Evaluate("=" & Format(CStr(Target.Value), "m/d"))
End If
End If

End With
ErrorHalt:
Application.EnableEvents = True
End Sub

clarksonneo
04-02-2011, 06:16 PM
It looks like the problem isn't in data Validation, but that when you type "3/2" into a cell, Excel "sees" March 2 rather than 1.5 or when you type "100/2" into a cell, it sees a string rather than 50.

Perhaps something like

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHalt
With Target
If .Cells.Count <> 1 Then ErrorHalt
Application.EnableEvents = False

If Not (IsNumeric(.Text)) Then
If Val(CStr(.Value)) <> 0 Then
.Value = Evaluate("=" & Format(CStr(Target.Value), "m/d"))
End If
End If

End With
ErrorHalt:
Application.EnableEvents = True
End Sub

hi,

what do I need to do after I copy and paste it in a module?

thanks

mikerickson
04-02-2011, 06:50 PM
That would go in the sheet's code module, not a normal module.

Bob Phillips
04-03-2011, 04:05 AM
It looks like the problem isn't in data Validation, but that when you type "3/2" into a cell, Excel "sees" March 2 rather than 1.5 or when you type "100/2" into a cell, it sees a string rather than 50.

Not if the cell is formatted as text, as I had assumed.

mikerickson
04-03-2011, 07:21 AM
It appears that the cells are not formatted as text
....
"Form2=a string that begins with a numeral and ends with "/2"?"
not string.
if I input =200/2, i want the result is 100
Excel's assumptions and auto-type conversion when it "sees" the user enter "3/2" argues for a consistent style of data entry.

Bob Phillips
04-03-2011, 08:38 AM
It appears that the cells are not formatted as text
....
"Form2=a string that begins with a numeral and ends with "/2"?"
not string.
if I input =200/2, i want the result is 100
Excel's assumptions and auto-type conversion when it "sees" the user enter "3/2" argues for a consistent style of data entry.

I had answered before that information was disclosed, and so the assumptions had already been made.