Data Validation - Again!

Once again I'm struggling with the 'Custom' option in Data Validation.

I have a cell formatted as 'Date' (assume A1).

I want the user to be able to enter any valid date greater than (say)
01/01/2000.

Alternatively, I also want the user to be able to enter a single character
(the letter 'X' for example) instead of a date, which will be used by
another formula to perform an alternative date function.

I have been trying numerous variations of the following custom formulas:

1. =AND(UPPER(A1)="X",A1>=DATE(2000,1,1))

2. =OR(UPPER(A1)="X",A1>=DATE(2000,1,1))

I can get the letter 'X' (upper or lower case) to validate correctly, but
not the date.

Maybe I'm not understanding how Data Validation works.

I assume that the data entered into the cell has to match what the
validation formula is looking for (which would suggest that formula 2. is
the one to use - either the data is an 'X', OR it is a date greater than
01/01/2000, otherwise the data is incorrect).

Please can someone provide a working formula... and an explanation?

TIA
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-


.

Relevant Pages

  • Re: Unique Macro
    ... No need for code here - you can use data validation for this. ... set Allow to Custom and enter the custom ... > I am totally new to excel programming, having never needed to use it> before. ... > What I want to do, is impliment some sort of way, so that the same> item number can't be entered in the Item-No column more than once. ...
    (microsoft.public.excel.programming)
  • Excel2000: Custom data validation and named ranges
    ... Let's me have a custom data validation defined for some range of cells. ... I select the range A1:A10 and set custom validation formula as ... Maybe it's because I'm looking for particular entry in named range? ...
    (microsoft.public.excel.misc)
  • Re: Validation Criteria of "Custom" fails to work
    ... > it will always format as dates so you need to change in to General ... I wish to use data validation for a particular cell. ... I chose "custom", there is a formula. ... "Circle Invalid Data", it still won't work. ...
    (microsoft.public.excel)
  • Re: Validation Criteria of "Custom" fails to work
    ... I wish to use data validation for a particular cell. ... I chose "custom", there is a formula. ... "Circle Invalid Data", it still won't work. ...
    (microsoft.public.excel)
  • Re: Data Validation - Prevent Blank Cells
    ... > I am using the 'Custom' option with the COUNTIF function ... > Can I make sure that an entry is made? ... > require VBA module outside of Data Validation. ...
    (microsoft.public.excel.worksheet.functions)