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

You can put the table onto another sheet, but instead of using cell references, you use named ranges. Data Validation works with cell references, as long as they are in the same sheet, but stangly Data Validation will work if you use named ranges, which refer to cells in another sheet.