Is it possible to select from a table and then

[dgraham]

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

[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

"dgraham" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

[Biff]

Maybe this is what you had in mind:

http://contextures.com/xlDataVal03.html

Biff

"dgraham" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[dgraham]

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

[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

"dgraham" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

[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

"dgraham" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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

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

"dgraham" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[dgraham]

Hi Biff,

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


Regards

David

[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

[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


"dgraham" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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