Custom Data Val for specific text and numbers only
 

Hi Everyone

I need to create a custom data validation rule to allow the following data
types only:

1. numbers greater than or equal to 10; or
2. if the number is less than 10 then the user can only enter the text "<RL".

I have tried AND and OR statements however once the 2. condition has been
met ie: if the number is less than 10 then the user can only enter the text
"<RL" what I have found is if I enter any text value (not numeric that
condition works) say "abc" it is accepted - which is not what I want I only
want "<RL" to be accepted.

Please help...

Thank you in advance for any suggestions, Silena

Custom Data Val for specific text and numbers only
 

2. if the number is less than 10

What number? A number in another cell?

As you've explained it, it's not possible to set validation for a cell in
that manner.

--
Biff
Microsoft Excel MVP


"Silena K-K" wrote in message
...
Hi Everyone

I need to create a custom data validation rule to allow the following data
types only:

1. numbers greater than or equal to 10; or
2. if the number is less than 10 then the user can only enter the text
"<RL".

I have tried AND and OR statements however once the 2. condition has been
met ie: if the number is less than 10 then the user can only enter the
text
"<RL" what I have found is if I enter any text value (not numeric that
condition works) say "abc" it is accepted - which is not what I want I
only
want "<RL" to be accepted.

Please help...

Thank you in advance for any suggestions, Silena

Custom Data Val for specific text and numbers only
 

Hi Biff

The user enters any whole number 0 into cell D5 - I want a Data Val rule to
check whether the number entered in D5 is = the number in B10. If it is
that's good, if not then I want Data Val to prevent the user from entering
the number.

However, I don't want D5 left as an empty cell (because then my checkers
will wonder why there is no value in the cell), so what I want is for the
user to be able to enter a text value ie: "<RL" so that the checker knows the
number was less than the Reporting Limit and so was not entered.

I found a formula =OR(D5=B10,AND(D5="<RL") which works for comparing cell
D5 to B10 and preventing any numbers less than B10 being entered into cell D5.

The problem is however that once I have entered "<RL" into D5 the first
time, everytime after that if I enter any text value in cell D5 it is
accepted, but I don't want that. I only want it to accept <RL and values =
RL.

Regards, Silena

"T. Valko" wrote:

2. if the number is less than 10

What number? A number in another cell?

As you've explained it, it's not possible to set validation for a cell in
that manner.

--
Biff
Microsoft Excel MVP


"Silena K-K" wrote in message
...
Hi Everyone

I need to create a custom data validation rule to allow the following data
types only:

1. numbers greater than or equal to 10; or
2. if the number is less than 10 then the user can only enter the text
"<RL".

I have tried AND and OR statements however once the 2. condition has been
met ie: if the number is less than 10 then the user can only enter the
text
"<RL" what I have found is if I enter any text value (not numeric that
condition works) say "abc" it is accepted - which is not what I want I
only
want "<RL" to be accepted.

Please help...

Thank you in advance for any suggestions, Silena

Custom Data Val for specific text and numbers only
 

1. numbers greater than or equal to 10; or
2. if the number is less than 10 then the user can only enter the text
"<RL".

Based on your orig. posting, this might suffice
Select col A, apply Data Validation Custom
Formula: =OR(AND(ISNUMBER(A1),A1=10),EXACT(A1,"<RL"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Custom Data Val for specific text and numbers only
 

Looks good Max


Gord

On Fri, 15 Feb 2008 16:44:02 -0800, Max wrote:

1. numbers greater than or equal to 10; or
2. if the number is less than 10 then the user can only enter the text
"<RL".

Based on your orig. posting, this might suffice
Select col A, apply Data Validation Custom
Formula: =OR(AND(ISNUMBER(A1),A1=10),EXACT(A1,"<RL"))

Custom Data Val for specific text and numbers only
 

Absolutely fabulous! Max you are brilliant - thanks Gord also for the thumbs
up!!!

Silena

"Max" wrote:

1. numbers greater than or equal to 10; or
2. if the number is less than 10 then the user can only enter the text
"<RL".

Based on your orig. posting, this might suffice
Select col A, apply Data Validation Custom
Formula: =OR(AND(ISNUMBER(A1),A1=10),EXACT(A1,"<RL"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Custom Data Val for specific text and numbers only
 

Hi Silena

Create a list of cells with <RL, 10, 11, 12 etc and name this myList
In DVSource=myList

--
Regards
Roger Govier

"Silena K-K" wrote in message
...
Hi Everyone

I need to create a custom data validation rule to allow the following data
types only:

1. numbers greater than or equal to 10; or
2. if the number is less than 10 then the user can only enter the text
"<RL".

I have tried AND and OR statements however once the 2. condition has been
met ie: if the number is less than 10 then the user can only enter the
text
"<RL" what I have found is if I enter any text value (not numeric that
condition works) say "abc" it is accepted - which is not what I want I
only
want "<RL" to be accepted.

Please help...

Thank you in advance for any suggestions, Silena

Custom Data Val for specific text and numbers only
 

Welcome, Silena. Glad it worked.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Silena K-K" wrote in message
...
Absolutely fabulous! Max you are brilliant - thanks Gord also for the
thumbs
up!!!

Silena

Custom Data Val for specific text and numbers only
 

Thanks, Gord.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Looks good Max


Gord