Multiple Data Validation

[Pisolippia]

Hi all,
I wish I could have multiple Data Validation for one cell.
The conditions I need to check are :
- That the value entered is at least 2 digits long
- That its numeric value is less than the value of another cell (constant).
Depending on any of the two conditions I need to have different message boxes.
Thanks in advance for your kind advice.

 

[lenze]

The first part is easy. Use the Custom Validation and the AND function. Something like =AND(Len(Cell)>1,Cell>RefCell). Getting two different messages is more challenging and prbably would best be done with the WorksheetChange Event.

 

[Pisolippia]

Thanks for your quick answer.

 

[Pisolippia]

Thanks for your quick answer.
I still have problems using your formula when I enter values 01 to 09 in my cell.
They're converted to 1-9 and I get the error box.

 

[Aladin Akyurek]

On 2002-03-29 08:18, Pisolippia wrote:
Thanks for your quick answer.
I still have problems using your formula when I enter values 01 to 09 in my cell.
They're converted to 1-9 and I get the error box.

Adjust lenze's formula to suit your situation:

=AND(LEN(Cell)>1,Cell+0<ComparisonCell)

 

[Pisolippia]

We're still not there.
Let try to explain better :
Input cell A1 has your suggested data validation formula :
AND(LEN(A1)>1,A1+0<$A$2)
Cell A2 has a constant value say 70
if I enter values in the range 10-69 they're
accepted.
If I enter values from 70 over I get the message box.
If I enter values from 01 to 09 (two digits) it looks they're missing the zero hence their length becomes < 2 and I get the message box which is not right.

 

[lenze]

Format A1 as Text. The "+0" in Aladin's formula converts it back to a number

 

[Pisolippia]

Great !
It works just as expected; will check again to see if someone has any ideas on how to get different message boxes for the same data validation.
Thanks and Happy Easter to you all.

 

[Aladin Akyurek]

On 2002-03-29 08:30, Pisolippia wrote:
We're still not there.
Let try to explain better :
Input cell A1 has your suggested data validation formula :
AND(LEN(A1)>1,A1+0<$A$2)
Cell A2 has a constant value say 70
if I enter values in the range 10-69 they're
accepted.
If I enter values from 70 over I get the message box.
If I enter values from 01 to 09 (two digits) it looks they're missing the zero hence their length becomes < 2 and I get the message box which is not right.

That's right.

If you insist entering the numbers as 01, 09, 11, etc., format A1 as Text. Then the formula

=AND(LEN(A1)>1,A1+0<$A$2)

will simply work.

If you format A1 as General, you can use a much simpler formula:

=A1<A2

Aladin

 

[lenze]

Aladin is correct. If you must display the cells as 05,09,etc., then you can use Custom Formating '00' to display the leading zero for numbers less than 10. This will eliminate the LEN() part of the formula, reducing it to A1<A2
This message was edited by lenze on 2002-03-29 09:02
This message was edited by lenze on 2002-03-29 09:03