Multiple Data Validation

Pisolippia · Mar 29th, 2002, 08:56 AM

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 · Mar 29th, 2002, 09:00 AM

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 · Mar 29th, 2002, 09:16 AM

Thanks for your quick answer.

Pisolippia · Mar 29th, 2002, 09:18 AM

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 · Mar 29th, 2002, 09:21 AM

Quote:

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

Pisolippia · Mar 29th, 2002, 09:30 AM

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 · Mar 29th, 2002, 09:32 AM

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

Pisolippia · Mar 29th, 2002, 09:37 AM

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 · Mar 29th, 2002, 09:46 AM

Quote:

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
Aladin

lenze · Mar 29th, 2002, 10:02 AM

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
[ This Message was edited by: lenze on 2002-03-29 09:02 ]

[ This Message was edited by: lenze on 2002-03-29 09:03 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 08:56 AM	#1
Pisolippia New Member Join Date: Mar 2002 Location: ITALY Posts: 16	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.

Mar 29th, 2002, 09:00 AM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	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.

Mar 29th, 2002, 09:16 AM	#3
Pisolippia New Member Join Date: Mar 2002 Location: ITALY Posts: 16	Thanks for your quick answer.

Mar 29th, 2002, 09:18 AM	#4
Pisolippia New Member Join Date: Mar 2002 Location: ITALY Posts: 16	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.

Mar 29th, 2002, 09:30 AM	#6
Pisolippia New Member Join Date: Mar 2002 Location: ITALY Posts: 16	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.

Mar 29th, 2002, 09:32 AM	#7
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	Format A1 as Text. The "+0" in Aladin's formula converts it back to a number

Mar 29th, 2002, 09:37 AM	#8
Pisolippia New Member Join Date: Mar 2002 Location: ITALY Posts: 16	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.

Mar 29th, 2002, 10:02 AM	#10
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	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 [ This Message was edited by: lenze on 2002-03-29 09:02 ] [ This Message was edited by: lenze on 2002-03-29 09:03 ]