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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.