Custom Data Val for specific text and numbers only

[Silena K-K]

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

 

[T. Valko]

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.

 

[Silena K-K]

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

 

[Max]

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

---

 

[Gord Dibben]

Looks good Max


Gord

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

 

[Silena K-K]

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

Silena

 

[Roger Govier]

Hi Silena

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

 

[Max]

Welcome, Silena. Glad it worked.

 

[Max]

Thanks, Gord.