Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of table depending on a drop-down-list entry
Dear Experts:
I got a drop-down combo box with three entries (DE, EN, ES). As soon as the user selects one of these entries a table on the same worksheet has to be sorted by the entry chosen. Example: Combobox entries a DE, EN, ES The list to be sorted has the following make-up PRODUCT COUNTRY Item 1 DE Item 2 ES Item 3 DE Item 3 EN Item 5 ES Task: as soon as the user selects a value of the drop-down list, the table (on the same worksheet: A1:B6) is to be sorted by that field value (using VBA). Help is much appreciated. Thank you very much in advance. Regards, Andreas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of table depending on a drop-down-list entry
Andreas,
You can't actually sort by an item - you sort by the entire list, and you can only sort ascending or descending or by a specific order that you create using custom lists. To do what you want, you would need to add a column of formulas to return a value that will sort the way that you want. Something like =IF(B2=$C$1,1,0) copied down to match your list, where C1 will contain the value from the combobox. Then you could sort the data table based on that column, in descending order. But perhaps what you really want is to filter the table to show just the rows where the country value equals the combo box value. Of course, the code to do those two different actions differs, so post back with what you actually want. HTH, Bernie MS Excel MVP "andreashermle" wrote in message ... Dear Experts: I got a drop-down combo box with three entries (DE, EN, ES). As soon as the user selects one of these entries a table on the same worksheet has to be sorted by the entry chosen. Example: Combobox entries a DE, EN, ES The list to be sorted has the following make-up PRODUCT COUNTRY Item 1 DE Item 2 ES Item 3 DE Item 3 EN Item 5 ES Task: as soon as the user selects a value of the drop-down list, the table (on the same worksheet: A1:B6) is to be sorted by that field value (using VBA). Help is much appreciated. Thank you very much in advance. Regards, Andreas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of table depending on a drop-down-list entry
On Oct 2, 3:43*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Andreas, You can't actually sort by an item - you sort by the entire list, and you can only sort ascending or descending or by a specific order that you create using custom lists. To do what you want, you would need to add a column of formulas to return a value that will sort the way that you want. *Something like =IF(B2=$C$1,1,0) copied down to match your list, where C1 will contain the value from the combobox. *Then you could sort the data table based on that column, in descending order. But perhaps what you really want is to filter the table to show just the rows where the country value equals the combo box value. Of course, the code to do those two different actions differs, so post back with what you actually want. HTH, Bernie MS Excel MVP "andreashermle" wrote in message ... Dear Experts: I got a drop-down combo box with three entries (DE, EN, ES). As soon as the user selects one of these entries a table on the same worksheet has to be sorted by the entry chosen. Example: Combobox entries a DE, EN, ES The list to be sorted has the following make-up PRODUCT *COUNTRY Item 1 * * * * DE Item 2 * * * * ES Item 3 * * * * DE Item 3 * * * * EN Item 5 * * * * ES Task: as soon as the user selects a value of the drop-down list, the table (on the same worksheet: A1:B6) is to be sorted by that field value (using VBA). Help is much appreciated. Thank you very much in advance. Regards, Andreas- Hide quoted text - - Show quoted text - Dear Bernie, ooops, english being not my mother tongue I sometimes make mistakes. Of course 'filtering the list' is meant and not 'sorting', i.e. I would like to filter the list for the value that was selected from the combo box. Thank you in advance for your help. Regards, Andreas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of table depending on a drop-down-list entry
Andreas,
Copy the code below, right-click the sheet tab, select "View Code" (whatever the equivalent in your Excel), then paste the code into the window that appears. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$1" Then Exit Sub Range("D4:D13").AutoFilter Field:=1, Criteria1:=Range("A1").Value End Sub Change the $A$1 and A1 to the address of the cell that you are using to select the country code, and change the D4:D13 to the address of the list of countries - with the D4 being the title cell for the column. If the list can grow longer, you could change the code to Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$1" Then Exit Sub Raange(Range("D4", Range("D4").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("A1").Value End Sub -- HTH, Bernie MS Excel MVP "andreas-hermle" wrote in message ... On Oct 2, 3:43 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Andreas, You can't actually sort by an item - you sort by the entire list, and you can only sort ascending or descending or by a specific order that you create using custom lists. To do what you want, you would need to add a column of formulas to return a value that will sort the way that you want. Something like =IF(B2=$C$1,1,0) copied down to match your list, where C1 will contain the value from the combobox. Then you could sort the data table based on that column, in descending order. But perhaps what you really want is to filter the table to show just the rows where the country value equals the combo box value. Of course, the code to do those two different actions differs, so post back with what you actually want. HTH, Bernie MS Excel MVP "andreashermle" wrote in message ... Dear Experts: I got a drop-down combo box with three entries (DE, EN, ES). As soon as the user selects one of these entries a table on the same worksheet has to be sorted by the entry chosen. Example: Combobox entries a DE, EN, ES The list to be sorted has the following make-up PRODUCT COUNTRY Item 1 DE Item 2 ES Item 3 DE Item 3 EN Item 5 ES Task: as soon as the user selects a value of the drop-down list, the table (on the same worksheet: A1:B6) is to be sorted by that field value (using VBA). Help is much appreciated. Thank you very much in advance. Regards, Andreas- Hide quoted text - - Show quoted text - Dear Bernie, ooops, english being not my mother tongue I sometimes make mistakes. Of course 'filtering the list' is meant and not 'sorting', i.e. I would like to filter the list for the value that was selected from the combo box. Thank you in advance for your help. Regards, Andreas |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of table depending on a drop-down-list entry
On Oct 2, 4:57*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Andreas, Copy the code below, right-click the sheet tab, select "View Code" (whatever the equivalent in your Excel), then paste the code into the window that appears. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$1" Then Exit Sub Range("D4:D13").AutoFilter Field:=1, Criteria1:=Range("A1").Value End Sub Change the $A$1 and A1 to the address of the cell that you are using to select the country code, and change the D4:D13 to the address of the list of countries - with the D4 being the title cell for the column. If the list can grow longer, you could change the code to Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$1" Then Exit Sub Raange(Range("D4", Range("D4").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("A1").Value End Sub -- HTH, Bernie MS Excel MVP "andreas-hermle" wrote in message ... On Oct 2, 3:43 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Andreas, You can't actually sort by an item - you sort by the entire list, and you can only sort ascending or descending or by a specific order that you create using custom lists. To do what you want, you would need to add a column of formulas to return a value that will sort the way that you want. Something like =IF(B2=$C$1,1,0) copied down to match your list, where C1 will contain the value from the combobox. Then you could sort the data table based on that column, in descending order. But perhaps what you really want is to filter the table to show just the rows where the country value equals the combo box value. Of course, the code to do those two different actions differs, so post back with what you actually want. HTH, Bernie MS Excel MVP "andreashermle" wrote in message .... Dear Experts: I got a drop-down combo box with three entries (DE, EN, ES). As soon as the user selects one of these entries a table on the same worksheet has to be sorted by the entry chosen. Example: Combobox entries a DE, EN, ES The list to be sorted has the following make-up PRODUCT COUNTRY Item 1 DE Item 2 ES Item 3 DE Item 3 EN Item 5 ES Task: as soon as the user selects a value of the drop-down list, the table (on the same worksheet: A1:B6) is to be sorted by that field value (using VBA). Help is much appreciated. Thank you very much in advance. Regards, Andreas- Hide quoted text - - Show quoted text - Dear Bernie, ooops, english being not my mother tongue I sometimes make mistakes. Of course 'filtering the list' is meant and not 'sorting', i.e. I would like to filter the list for the value that was selected from the combo box. Thank you in advance for your help. Regards, Andreas- Hide quoted text - - Show quoted text - Dear Bearnie, great. It is working as desired. Thank you very much for your professional help. Regards, Andreas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LIST ENTRY ENABLE DROP DOWN LIST TO ACCEPT MORE THAN 1 ENTRY | Excel Discussion (Misc queries) | |||
how do I link a drop down list entry to a new drop down cell? | Excel Discussion (Misc queries) | |||
display text depending on coice from drop down list | Excel Discussion (Misc queries) | |||
return a data list depending on a value selected within a drop dow | Excel Discussion (Misc queries) | |||
Program a drop down list to show a specific response depending on the selction | Excel Programming |