Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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")) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom cell formats combination of numbers and text | Excel Discussion (Misc queries) | |||
custom format cell with text and numbers | Excel Discussion (Misc queries) | |||
CONDITIONAL FORMATING FINDING TEXT CONTAINING SPECIFIC NUMBERS | Excel Worksheet Functions | |||
Numbers after specific text | Excel Worksheet Functions | |||
How to format text and numbers as custom | Excel Discussion (Misc queries) |