Data Validation Question

D

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
 
B

Bryan Hessey

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

--
 
D

dgraham

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

David
 
B

Bryan Hessey

Whatever the name of the sheet you choose to use to store the numbers
yes.


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

--
 
D

dgraham

Thank Brian,

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

Regards

Davi
 
B

Bryan Hessey

.. . ... 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

--
 
G

Gary Brown

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.
 
Top