Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Items in drop-down list alphabetized
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Items in drop-down list alphabetized
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 "andy62" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Items in drop-down list alphabetized
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 On Fri, 9 Feb 2007 11:16:00 -0800, andy62 wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Items in drop-down list alphabetized
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:'Sh eet1'!B1,'Sheet1'!B1)1,"",IF(ISNUMBER('Sheet1'!B1 +0),'Sheet1'!B1+0,CODE(LEFT('Sheet1'!B1))*10^10+RO W()))) 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. "andy62" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Items in drop-down list alphabetized
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" wrote in message ... 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:'Sh eet1'!B1,'Sheet1'!B1)1,"",IF(ISNUMBER('Sheet1'!B1 +0),'Sheet1'!B1+0,CODE(LEFT('Sheet1'!B1))*10^10+RO W()))) 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. "andy62" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Items in drop-down list alphabetized
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" wrote: 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" wrote in message ... 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:'Sh eet1'!B1,'Sheet1'!B1)1,"",IF(ISNUMBER('Sheet1'!B1 +0),'Sheet1'!B1+0,CODE(LEFT('Sheet1'!B1))*10^10+RO W()))) 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. "andy62" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Items in drop-down list alphabetized
Depends on how big a "big range" is and what other types of calculations are
taking place. Biff "andy62" wrote in message ... 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" wrote: 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" wrote in message ... 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:'Sh eet1'!B1,'Sheet1'!B1)1,"",IF(ISNUMBER('Sheet1'!B1 +0),'Sheet1'!B1+0,CODE(LEFT('Sheet1'!B1))*10^10+RO W()))) 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. "andy62" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list shrinks as you go down column | Excel Discussion (Misc queries) | |||
How do i set up a drop down list and hide my list data. | Excel Worksheet Functions | |||
Hide previously used items from multiple drop lists | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
automatic color change in cells using a drop down list | Excel Worksheet Functions |