Hi,

I was wanting to enter into a worksheet numbers rangeing from 100 to
300, the numbers must be validated to the 100-300 numbers and once
entered into the worksheet that specific number would then be removed
from the list so it would not be possible to have a duplicate number
entered.

your assistance would be appreciated

Regards

David


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

Hi!

Select the range of cells where you want this to apply. Assume this range is
A1:A5.

Select the range A1:A5
Goto DataValidation
Allow: Custom
Formula: =AND(A1=100,A1<=300,COUNTIF(A$1:A$5,A1)<2)
OK

Biff

"dgraham" wrote in
message ...

Hi,

I was wanting to enter into a worksheet numbers rangeing from 100 to
300, the numbers must be validated to the 100-300 numbers and once
entered into the worksheet that specific number would then be removed
from the list so it would not be possible to have a duplicate number
entered.

your assistance would be appreciated

Regards

David


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

Thanks Biff,

My example said 100 - 300 but the actual numbers will not be in
sequence ie.. 700 -755, then 800- 890, then 900 - 999 etc.. so I would
need to enter the number, validate it to an existing number held maybe
in another sheet, pop a message if the number was not valid and when
each correct number is entered those numbers are removed from the list
to avoid duplicate entries, I'm not sure if this is even possible in
Excel.

Regards

David


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

Maybe this is what you had in mind:

http://contextures.com/xlDataVal03.html

Biff

"dgraham" wrote in
message ...

Thanks Biff,

My example said 100 - 300 but the actual numbers will not be in
sequence ie.. 700 -755, then 800- 890, then 900 - 999 etc.. so I would
need to enter the number, validate it to an existing number held maybe
in another sheet, pop a message if the number was not valid and when
each correct number is entered those numbers are removed from the list
to avoid duplicate entries, I'm not sure if this is even possible in
Excel.

Regards

David


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

Hi, thanks again for your help, I have set up the worksheet folowing
the information at the link you provided and it works well, except,
using a dropdown list is not suitable for my worksheet as there are
about 500 numbers and it would be quicker just to type the numbers in
rather than selecting it from the listbox. I unchecked the in-cell
dropdown but when i enter the correct numbers i get the error message
when i hit enter (i can click in another cell with the mouse and it
works fine, but would like it to work by hitting the enter key)
otherwise it is working fine. do you know if this is a bug or is there
another setting i'm missing.


Regards

David


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

Sorry, but I'm not understanding you.

Based on your first post I thought you just wanted to eliminate any dupe
entries so I suggested using data validation.

In your reply, that isn't what you wanted so I was guessing that what you
might want was found at the link I posted.

So, I don't know (understand) what it is you're really wanting to do.

Biff

"dgraham" wrote in
message ...

Hi, thanks again for your help, I have set up the worksheet folowing
the information at the link you provided and it works well, except,
using a dropdown list is not suitable for my worksheet as there are
about 500 numbers and it would be quicker just to type the numbers in
rather than selecting it from the listbox. I unchecked the in-cell
dropdown but when i enter the correct numbers i get the error message
when i hit enter (i can click in another cell with the mouse and it
works fine, but would like it to work by hitting the enter key)
otherwise it is working fine. do you know if this is a bug or is there
another setting i'm missing.


Regards

David


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

Sorry Biff,

Ok this it what i want to do:

I have a list of buses numbered something like this 320-360, 700-750,
800-899, 900-990.

each day I want to enter them into a worksheet call "Shed" which is how
they are parked ie.. bus 320 in A1, 321, in B1, .... bus 700 in A2, 701
in B2 etc....

I only want to be able to enter the bus number once so it won't
allocated in the shed more than once. (the info provided before does
this, but with a dropdown list wich is not suitable as there are about
500 buses and it would be easier to type the number then hit the enter
key.

as i mentioned in my last, everything works fine except after I enter a
valid number and press enter I get the error message unless I click in
another cell with the mouse. I should be able to just press enter and
it should accept it as it is a valid number.

I hope I havn't waffled on too much. but that is basicly what I was
wanting to do.

Regards

David


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

Ok, forget about the drop down list method.

So, is a particular row designated for only a certain bus series? Like:

Row1 = 320-360
Row2 = 700-750
Row3 = 800-899

Or, can any bus number appear in any row?

We'll get this figured out!

Biff

"dgraham" wrote in
message ...

Sorry Biff,

Ok this it what i want to do:

I have a list of buses numbered something like this 320-360, 700-750,
800-899, 900-990.

each day I want to enter them into a worksheet call "Shed" which is how
they are parked ie.. bus 320 in A1, 321, in B1, .... bus 700 in A2, 701
in B2 etc....

I only want to be able to enter the bus number once so it won't
allocated in the shed more than once. (the info provided before does
this, but with a dropdown list wich is not suitable as there are about
500 buses and it would be easier to type the number then hit the enter
key.

as i mentioned in my last, everything works fine except after I enter a
valid number and press enter I get the error message unless I click in
another cell with the mouse. I should be able to just press enter and
it should accept it as it is a valid number.

I hope I havn't waffled on too much. but that is basicly what I was
wanting to do.

Regards

David


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

Hi Biff,

Hope we can, no the buses can end up anywhere in the shed (row).

As they are put away by the refuelers i would go and enter them where
they have been parked.

regards

David


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

Ok........

If any bus number can appear in any cell on any row.........

I have a list of buses numbered something like this 320-360, 700-750,
800-899, 900-990.

800-899, 900-990

That is one continuous range so:

Assume the entire range is A1:E5

Select that range
Goto DataValidation
Allow: Custom
Formula:

=AND(OR(AND(A1=320,A1<=360),AND(A1=700,A1<=750), AND(A1=800,A1<=990)),COUNTIF($A$1:$E$5,A1)<2)

Biff

"dgraham" wrote in
message ...

Hi Biff,

Hope we can, no the buses can end up anywhere in the shed (row).

As they are put away by the refuelers i would go and enter them where
they have been parked.

regards

David


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

Hi Biff,

Thanks heaps, that works fine now. your help was much appreciated.


Regards

David


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

Hi,

I'm trying to make data validation more user friendly, as other will be
using and updating the worksheet, I'm using the following in the custom
validation:
"=AND(OR(AND(B3=$M$2,B3<=$M$250),AND(B3=$N$2,B3< =$N$250),AND(B3=$O$2,B3<=$O$250),AND(B3=$P$2,B3< =$P$23),AND(B3=$Q$2,B3<=$Q$23),AND(B3=$R$2,B3<=$ R$23)),COUNTIF($B$3:$E$46,B3)<2)

it works fine except that data between the numbers listed will still be
accepted. ie.. if I have 320, 321, 323, 324. in M2:M5, 322 will be
allowed to be entered. Is there a way to only have the numbers in the
list be accepted.

Regards

David


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

=ISNUMBER(MATCH($B$3,$M$2:$M$5,0))

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"dgraham" wrote in
message ...

Hi,

I'm trying to make data validation more user friendly, as other will be
using and updating the worksheet, I'm using the following in the custom
validation:
"=AND(OR(AND(B3=$M$2,B3<=$M$250),AND(B3=$N$2,B3< =$N$250),AND(B3=$O$2,B3<=$O$250),AND(B3=$P$2,B3< =$P$23),AND(B3=$Q$2,B3<=$Q$23),AND(B3=$R$2,B3<=$ R$23)),COUNTIF($B$3:$E$46,B3)<2)

it works fine except that data between the numbers listed will still be
accepted. ie.. if I have 320, 321, 323, 324. in M2:M5, 322 will be
allowed to be entered. Is there a way to only have the numbers in the
list be accepted.

Regards

David


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

thanks Peo Sjoblom,

I guess I'm doing somthing wrong, I get an error when iI use that code
on its own or in combination with my current code. any assistance is
appreciated.

Regards

David


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