Excel Drop List

[dakotauk]

I have a little problem with my spreadsheet, I have made a dropdown menu too help inputting data in the spreadsheet, only prob is I have over 3000 cells selected as the source but its only showing half of them in the drop down list, is there a size cap or is there a way of increasing it??

Chars

Gaz

 

[SkipVought]

Hi,

What kind of control? Form, Control Toolbox, Data Validation???

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

AFAIK, dropdown lists are restricted to 1000 entries. To be honest, how is a drop down list with 3000 entries actually helping the data entry ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dakotauk]

validation method, its to process job sheets its done on numerical accounts numbers so the person entering the info is forced to put correct account in.

 

[xlbo]

yes but it will take them how long to find the value in a 3000 item list ??

Is there not some logic you can apply to the range of numbers available rather than making the poor user scroll through 3000 entries to find the 1 they need ?

How do you ensure that they ahve selected the correct account number out of the 3000 available ?

In any case, you are still stuck with a 1000 item limit....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

a 1000 item limit???? Good grief, I didn't know that. If someone gave me anything with 1000 items in a dropdown...I would hand it back and refuse to use it. That is absurd. IMO, anything over 50 is outrageous and is likely indicative of poor use of logic and design. Do users actually have dropdowns with that many item to go through?

Gerry
My paintings and sculpture

 

[dakotauk]

well if its a stupid question whats the best step forward!!

 

[xlbo]

It's not a stupid question - it's just that I have experience of being a user and also developing these kind of things. It is natural to want to try and ensure data integrity but the way you are trying to do it at the mo would, IMHO, be a large burden on the users

Other things you might want to look at are:

whether there is any logic that determines the number range available.

If there is another identifier that is easier to recognise than an acct number - if so, you may be able to use a VLOOKUP to return this data and display to the user on entry of the acct no.

Can you give any further details on the logic of what is meant to happen and what the users are meant to be doing. There is almost certainly something that can be done but exactly what will depend on your setup and process....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question