How do i create a drop down list to sort in Excel?

Question

I am creating a spreadsheet for work which contains all different customers on it. The top row is frozen, and i am trying to make a pull down menu on that top row so i can just click on a certain customer and have all of them come up. I know that it can be done, I just can't figure out how. I figured out how to create the drop down list through the Validate option, but that just fills in that cell with whatever customer I choose, I am trying to sort them. Can someone please help!!

Answer 1

Highlight the top row, then hit data>filter>autofilter

Answer 2

In general, try to avoid nesting multiple IF statements together. That's your clue that you should be taking a different approach to your problem. A table (any rectangular grouping of cells) combined with the VLOOKUP function is much less of a hassle, and allows you to keep all of your conditions in one place, rather than across numerous long functions. Supposing you can use Sheet2 to keep a list of options and values: A . . . . . . . . . . . . . . . . B Course Lecture (new) . . 20 Course Lecture (same) . 6 [and so on] you can use the formula =IF(AC1,VLOOKUP(AC1,'Sheet2'!A1:B19,2,... finished") First the formula checks if AC1 contains a value. If it does, it uses VLOOKUP to check the table on Sheet2, using the value in AC1, to get the value from the 2nd column (B in this case) that is an exact match (the fourth parameter "false"). If it doesn't, it returns "not finished". You can use column A for the drop down, avoiding cases where there is a value but it isn't one of your choices. There are other "lookup" functions for other cases, all of which are better than a bunch of IF checks.

Answer 3

For the best answers, search on this site https://shorturl.im/avH7C



You don't say whether you wish to do this for AC1 only, or for a range of rows in column AC1. Here is a way to do as you wish in either case. This code has only the two examples you gave in your question. For the other 16 responses, simply add a 'set' of code lines for each one like the two in the event handler. Note: the 'case' structure in this code is critical. Replicate the new sets using the same 'case' as in the first two. For AC1 only: Copy this event handler code to the clipboard: Private Sub Worksheet_Change(ByVal Target As Range) Select Case UCase(Range("AC1").Value) Case Is = "COURSE LECTURE (NEW)" Range("AI1").Value = 20 Case Is = "COURSE LECTURE (SAME)" Range("AI1").Value = 6 Case Else End Select End Sub Select the appropriate worksheet and right click the sheet tab. Select 'View Code'. Paste the event handler code into the editing area to the right. Close the VBE and return to the worksheet. Save the workbook. Make a selection in AC1 and the appropriate value will be returned in AI1. =============== For all rows in column AC containing a drop down with the 18 choices, use the following code and follow the process listed above. Private Sub Worksheet_Change(ByVal Target As Range) Dim i, LastRow LastRow = Range("AC" & Rows.Count).End(xlUp).Row Application.EnableEvents = False For i = 1 To LastRow Select Case UCase(Range("AC" & i).Value) Case Is = "COURSE LECTURE (NEW)" Range("AI" & i).Value = 20 Case Is = "COURSE LECTURE (SAME)" Range("AI" & i).Value = 6 Case Else End Select Next Application.EnableEvents = True End Sub

Answer 4

Thank you!!!!