Items in drop-down list alphabetized

[andy62]

I have a data validation which uses a (dynamic) drop-down list of up to 1500
names. Those names will not be entered alphabetically, and the worksheet is
protected and thus prevents the user from executing any sort to correct this.
But if it's not alphabeticized, it becomes impossible to find anything. Is
there any way to have the items in the drop-down appear in alphabetized
order? If not, what is the best way to address this? Can I point the data
validation to a "shadow" list of names which references and alphabetizes the
list the user actually enters? Create a user-executed macro which turns off
protection, alphabetizes the list, and turns protection back on?

TIA

 

[Mike]

Probably the best way is to sort the data using the workbook open evebt

Private Sub Workbook_Open()
Worksheets("yoursheet").Unprotect Password:="Yourpassword"
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Worksheets("yoursheet").Protect Password:="Yourpassword"
End Sub

 

[Gord Dibben]

Andy

Have you thought about using a Combobox instead?

With this method you have "Autocomplete" and resizing to show more items and you
can change the font size.

http://www.contextures.on.ca/xlDataVal10.html

Or download a sample workbook that allows you to add an item to the DV list and
have the list automatically re-sorted.

http://www.contextures.on.ca/excelfiles.html#DataVal

Scroll down to DV0012 - Update Validation List


Gord Dibben MS Excel MVP

 

[andy62]

Thanks to Mike and Gord for the ideas. As it turns out, for an unrelated
reason, I could not alphabeticize the range which the data validation
references (that data has to stay in the order in which it was keyed).
Through another post, I found a discussion elsewhere between MAX (?) and our
own Biff, which provided the answer. Here's a recap.

Problem: Items in a named range are used in data validation drop-downs
throughout the file, but want those drop-downs to present the items
alphabetically, even though the range used as the source is not alphabetized.

Explanation: Create an extra sheet (Sheet2) which uses formulas in Columns
A, B, and C to maintain an alphabetized version of the items from the range
in Sheet1. Column A essentially generates a unique numerical code for each
item in the range on Sheet1. Column B uses those codes in A to look up and
displays the items, smallest to largest. Column C counts the items in the
range so that a dynamic range can be created (the normal approach using the
Offset function won't work in this case, because the "blank" cells after the
last item contain formulas, and thus are really not blank).

Formulas: Here are the formulas I used. Note that they go into Sheet2 (and
note that the list of items in Sheet1 is in column B).

Column A, place in A1 and copy down to cover the entire possible range in
Sheet1:
=IF('Sheet1'!B1="","",IF(COUNTIF('Sheet1'!$B$1:'Sheet1'!B1,'Sheet1'!B1)>1,"",IF(ISNUMBER('Sheet1'!B1+0),'Sheet1'!B1+0,CODE(LEFT('Sheet1'!B1))*10^10+ROW())))

Column B, place in B1 and copy down to cover the entire possible range in
Sheet1: =IF(ROW()>COUNT(A:A),"",INDEX('All
Data'!B:B,MATCH(SMALL(A:A,ROW()),A:A,0)))

Column C, place in C1 only (no copy down): =1504-COUNTBLANK(B1:B1504)
(note: the max range I was using was 1504 items; there should be a way to do
this without hard-coding that number, but it was eluding me so I used it.
You'd have to put in your own maximum, or fix the formula)

Finally, to use this as a "dynamic range" in a data validation and not have
the drop-down list have a ton of blank entries, name the list of items and
when you define the name (Insert . . . Name . . . Define), use a formula like
this:

=INDIRECT("Sheet2!B2:B"&Sheet2!$C$1)

Hopefully, someoneday this will help someone who is persistent enough to
read and apply it.

 

[T. Valko]

I remember that discussion. It was about extracting unique entries and
sorting them.

If your list contains only unique entries then it's much easier than what we
talked about in that post.

Here's a link to a more recent post on the subject:

http://tinyurl.com/2c5pn5

Biff

 

[andy62]

Arrgghhh.

I finally noticed that my method only alphabeticizes by the first letter, so
if Jill came after Joe in the source range, she would still be second in my
"alphabeticized" range. So I tried to switch over to the approach in Biff's
tinyurl reference, but it crushed the memory on my laptop. Does it seem
right that that approach, when applied to a big range needing to be
alphabeticized, would consume so much memory?

 

[T. Valko]

Depends on how big a "big range" is and what other types of calculations are
taking place.

Biff