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


--
dgraham
------------------------------------------------------------------------
dgraham's Profile: http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=536648

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

--

dgraham Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=536648

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

David


--
dgraham
------------------------------------------------------------------------
dgraham's Profile: http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=536648

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=S heet4!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)

--

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

David


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=536648

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


--
dgraham
------------------------------------------------------------------------
dgraham's Profile: http://www.excelforum.com/member.php...o&userid=33138
View this thread: http://www.excelforum.com/showthread...hreadid=536648

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

--

dgraham Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=536648

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.


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=536648