Data Validation Question

Hi, I have the following formula that i use, is there a way to have the numbers in a worksheet as they will change ie.. numbers will be added and removed.

=AND(OR(AND(B3>=121,B3<=125),AND(B3>=149,B3<=156),AND(B3>=320,B3<=363),AND(B3>=700,B3<=799),AND(B3>=800,B3<=991)),COUNTIF($B$3:$E$46,B3)<2)

Regards

David

In (say) K1:L5 put
121 125
149 156
320 363
700 798
800 991

then use

=AND(OR(AND(B3>=K1,B3<=L1),AND(B3>=K2,B3<=L2), AND(B3>=K3,B3<=L3),AND(B3>=K4,B3<=L4),AND(B3>= K5,B3<=L5)),COUNTIF($B$3:$E$46,B3)<2)

HTH

--

Originally Posted by dgraham

Hi, I have the following formula that i use, is there a way to have the numbers in a worksheet as they will change ie.. numbers will be added and removed.

=AND(OR(AND(B3>=121,B3<=125),AND(B3>=149,B3<=156),AND(B3>=320,B3<=363),AND(B3>=700,B3<=799),AND(B3>=800,B3<=991)),COUNTIF($B$3:$E$46,B3)<2)

Regards

David

Thanks, If I choose to use another sheet, would I put "Sheet1!" in front of the cell range.

David

Thank Brian,

When I use another sheet I get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria" I guess that means I can't do it?

Regards

David

. . ... Data Validation?
was not mentioned (except as a topic).

If you are using data validation on the data to check validity against the permitted ranges in the table you could duplicate the table into the current sheet, with (say) AK1:AL5 = Sheet4!AK1 etc

--

Originally Posted by dgraham

Thank Brian,

When I use another sheet I get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria" I guess that means I can't do it?

Regards

David