data validation - number or number / 2

[clarksonneo]

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]

Maybe this DV formula

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

[clarksonneo]

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]

If you really add the =, then use

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

[mikerickson]

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]

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
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
"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]

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

[VBA]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[/VBA]

[clarksonneo]

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

[vba]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[/vba]

hi,

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

thanks

[mikerickson]

That would go in the sheet's code module, not a normal module.

[Bob Phillips]

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]

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]

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.