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