Solved Using drop downs to sort in Excel?

June 28, 2011 at 03:48:27
Specs: Windows XP
I have a spreadsheet that populates the data fields based on a selection from a dropdown menu. Once the data fields are populated I would like to use dropdown menus to sort the data and generate a list based on the selected criteria - is this possible?

In this case the first selection would be a job function - this would populate with all qualified individuals and their associated data. From there I would like to be able to use dropdowns to perform the custom sort type function - i.e. By shift, then by something like seniority, then by...


See More: Using drop downs to sort in Excel?

Report •

✔ Best Answer
June 30, 2011 at 10:16:10
Here's something that might get yous tarted:

With Data that looks like this, and a Drop Down in A1 that allow the user to choose Name, Age or Size, the code below will sort the table in B1:D4 based on what the user chooses with the drop down.

     A     B        C      D

1   Age	  Name	   Age	   Size
2	 Charles   34	  Medium
3	 Anne	   41	  Small
4	 Brad	   53	  Large


Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
   myKey = Range("A1")
    Range("B1:D4").Sort Key1:=myKey, Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 28, 2011 at 06:47:57
I think we need a little more detail.

How are the data fields being populated? In other words, when you choose a job function from the drop down, how does the information for "all qualified individuals and their associated data" end up in the cells?

If you are going to post any example data, please click on the following line and read the instructions on how to post data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 29, 2011 at 23:20:49
The fields were being populated via lookup formulas, but I have abandoned this approach because some of these fields may need to be edited once the list is formed.

I am now looking at just having the data for all employees on the page and just using the dropdowns to sort it (for other people's convenience). The purpose of the sheet is to ensure overtime is distributed fairly and in accordance with policy, so I would just like to have a series of dropdowns that will sort the data for the other supervisors.


Report •

#3
June 29, 2011 at 23:41:41
It's going to require a VBA macro to do what you want.

If having macros in the worksheet is OK, you'll need to tell us if you know how to write/edit VBA code.

If you do, I'm sure we can offer some sample code to get you started.

If you don't, you'll need to be very specific about how the data is laid out in the worksheet and what you want the drop downs to do.

One concern that I already have is your comment about fields being edited once the list is formed.

If the fields are edited, how will they be returned to their original state for subsequent sorts?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 30, 2011 at 10:16:10
✔ Best Answer
Here's something that might get yous tarted:

With Data that looks like this, and a Drop Down in A1 that allow the user to choose Name, Age or Size, the code below will sort the table in B1:D4 based on what the user chooses with the drop down.

     A     B        C      D

1   Age	  Name	   Age	   Size
2	 Charles   34	  Medium
3	 Anne	   41	  Small
4	 Brad	   53	  Large


Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
   myKey = Range("A1")
    Range("B1:D4").Sort Key1:=myKey, Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question