Multiple data validation criteria?

[ker_01]

Working in 2003, but this workbook has to also work in 2007

For this project I have to avoid macros because users are remote and may not
enable macros.

I have a column which needs two different data validation criteria;

(1) ColumnA >Column B and ColumnA <Column C (I can do this with data
validation)

(2) ColumnA does not contain values at more than one decimal place
4.2 is ok, 4.21 is not
I can also do this with data validation (custom formula)

However, Excel appears limited in that I cannot apply more than one data
validation criteria. Is there any (non-VBA) workaround, or am hosed? I'm
considering the use of conditional formatting, but again this only provides a
visual prompt, and does not force the value to meet validation standards, and
unlike the data validation, there is no prompt to tell the user why the value
is inappropriate.

I tried setting up a mirror cell (D1=A1) and set the secondary data
validation criteria on D1, but changes in values passed via formula do not
appear to trigger the data validation check.

Thank you for any suggestions,
Keith

 

[teylyn]

Hi,

using custom validation, you should be able to combine these criteria
You say you already have a custom validation for (2), then just combin
like

=and(a1>b1,a1<c1,<your other custom formula>)

cheers


Working in 2003, but this workbook has to also work in 2007

 

[ker_01]

Thank you, that is probably what I'll end up doing. I had hoped for multiple
seperate criteria so I could take advantage of the data validation error
message (to provide a separate message for each type of validation), but it
doesn't appear that Excel was designed for that.
Best,
Keith

Hi,

using custom validation, you should be able to combine these criteria.
You say you already have a custom validation for (2), then just combine
like

=and(a1>b1,a1<c1,<your other custom formula>)

cheers


Working in 2003, but this workbook has to also work in 2007

For this project I have to avoid macros because users are remote and may not
enable macros.

I have a column which needs two different data validation criteria;

(1) ColumnA >Column B and ColumnA <Column C (I can do this with data
validation)

(2) ColumnA does not contain values at more than one decimal place
4.2 is ok, 4.21 is not
I can also do this with data validation (custom formula)

However, Excel appears limited in that I cannot apply more than one data
validation criteria. Is there any (non-VBA) workaround, or am hosed? I'm
considering the use of conditional formatting, but again this only provides a
visual prompt, and does not force the value to meet validation standards, and
unlike the data validation, there is no prompt to tell the user why the value
is inappropriate.

I tried setting up a mirror cell (D1=A1) and set the secondary data
validation criteria on D1, but changes in values passed via formula do not
appear to trigger the data validation check.

Thank you for any suggestions,
Keith

--
teylyn

Teylyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=180256

Microsoft Office Help

.