Is it possible to select from a table and then

D

dgraham

Hi,

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

your assistance would be appreciated

Regards

Davi
 
B

Biff

Hi!

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

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

Biff
 
D

dgraham

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
 
D

dgraham

Hi, thanks again for your help, I have set up the worksheet folowin
the information at the link you provided and it works well, except
using a dropdown list is not suitable for my worksheet as there ar
about 500 numbers and it would be quicker just to type the numbers i
rather than selecting it from the listbox. I unchecked the in-cel
dropdown but when i enter the correct numbers i get the error messag
when i hit enter (i can click in another cell with the mouse and i
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 ther
another setting i'm missing.


Regards

Davi
 
B

Biff

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
 
D

dgraham

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
 
B

Biff

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
 
D

dgraham

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
 
B

Biff

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.
Click to expand...

800-899, 900-990

That is one continuous range so:

Assume the entire range is A1:E5

Select that range
Goto Data>Validation
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
 
D

dgraham

Hi Biff,

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


Regards

David
 
D

dgraham

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
 
P

Peo Sjoblom

=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
 
D

dgraham

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
 
Top