Pull down with a spin - sorting

[Blieg]

Hello,

I've searched the archives and attempted to locate this because I'm sure someone has answered this, but....

I have a number of users who will be using a spreadsheet who are unfamiliar with Excel. I would like them to be able to select an option in a pull down menu, and have data sorted based on the corresponding selection.

I need all of the data because of position formulas in other areas and cannot use a filter, but can use a sort. I have the drop down list built (easy) but am having trouble figuring out how to tie it to a sort of specific columns.

Thank you very much!

 

[VoG]

Unclear. Is this a Data Validation list? Is the data contiguous?

 

[lenze]

Hi and welcome to the board!!!
Can you give some examples and ranges of your data. Specificly what columns to sort on for each item chosen.
I believe, however, there is probably a better way. The following will sort the data by the Column when the Header is DoubleClicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Row = 1 Then
Target.CurrentRegion.Sort , Order1:=xlAscending, Key1:=Target, Header:=xlYes
End If
End Sub

lenze

 

[Blieg]

Hi guys, and thanks for the welcome.

The pull down menu is a data validation list, there are 12 columns, each with data from a database covering an hour of activity.

The data validation list refers to five hidden cells where I pasted in the column headings. Because I need to keep the hourly summaries together, and I use formulas to refer to the "most efficient" hour, at the very bottom, and "least efficient", at the top, depending on the selected column to rank by, I'm having trouble.

Here's an example - sorted by Longest Waiting Call (Abandoned). If I sorted by Longest Waiting Call (Answered), the order would be different. If you need any more information please let me know! Thank you!

<table style="border-collapse: collapse; width: 611pt;" border="0" cellpadding="0" cellspacing="0" width="813"><col style="width: 48pt;" width="64"> <col style="width: 48pt;" width="64"> <col style="width: 40pt;" width="53"> <col style="width: 48pt;" width="64"> <col style="width: 54pt;" width="72"> <col style="width: 60pt;" span="2" width="80"> <col style="width: 65pt;" width="86"> <col style="width: 46pt;" span="2" width="61"> <col style="width: 47pt;" width="63"> <col style="width: 49pt;" width="65"> <tbody><tr style="height: 54pt;" height="72"> <td class="xl75" style="height: 54pt; width: 48pt;" width="64" height="72">Date</td> <td class="xl67" style="width: 48pt;" width="64">Time</td> <td class="xl68" style="width: 40pt;" width="53">Entered</td> <td class="xl68" style="width: 48pt;" width="64">Answered</td> <td class="xl68" style="width: 54pt;" width="72">Abandoned</td> <td class="xl69" style="width: 60pt;" width="80">Percentage Abandoned</td> <td class="xl71" style="width: 60pt;" width="80">Longest Waiting Call (Answered)</td> <td class="xl70" style="width: 65pt;" width="86">Longest Waiting Call (Abandoned)</td> <td class="xl81" style="width: 46pt;" width="61">Support Agents Available</td> <td class="xl81" style="width: 46pt;" width="61">DVM Agents Available</td> <td class="xl78" style="width: 47pt;" width="63">Total Quality Available</td> <td class="xl78" style="width: 49pt;" width="65">Total Skill Available</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl76" style="height: 12.75pt;" align="right" height="17">1/2/10</td> <td class="xl66" align="right">1:00 AM</td> <td class="xl64" align="right">12</td> <td class="xl64" align="right">9</td> <td class="xl64" align="right">3</td> <td class="xl65" align="right">25.00</td> <td class="xl72" align="right">9.73</td> <td class="xl72" align="right">14.03</td> <td class="xl64" align="right">2</td> <td class="xl64" align="right">2</td> <td class="xl79" align="right">47.8</td> <td class="xl79" align="right">19.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl76" style="height: 12.75pt;" align="right" height="17">1/6/10</td> <td class="xl66" align="right">9:00 PM</td> <td class="xl64" align="right">53</td> <td class="xl64" align="right">35</td> <td class="xl64" align="right">17</td> <td class="xl65" align="right">32.69</td> <td class="xl72" align="right">4.70</td> <td class="xl72" align="right">13.87</td> <td class="xl64" align="right">7</td> <td class="xl64" align="right">5</td> <td class="xl79" align="right">111.2</td> <td class="xl79" align="right">54.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl76" style="height: 12.75pt;" align="right" height="17">1/3/10</td> <td class="xl66" align="right">12:00 AM</td> <td class="xl64" align="right">26</td> <td class="xl64" align="right">17</td> <td class="xl64" align="right">13</td> <td class="xl65" align="right">43.33</td> <td class="xl72" align="right">16.05</td> <td class="xl72" align="right">11.05</td> <td class="xl64" align="right">3</td> <td class="xl64" align="right">3</td> <td class="xl79" align="right">64.9</td> <td class="xl79" align="right">29.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl76" style="height: 12.75pt;" align="right" height="17">1/2/10</td> <td class="xl66" align="right">3:00 PM</td> <td class="xl64" align="right">46</td> <td class="xl64" align="right">37</td> <td class="xl64" align="right">9</td> <td class="xl65" align="right">19.57</td> <td class="xl72" align="right">8.25</td> <td class="xl72" align="right">10.35</td> <td class="xl64" align="right">5</td> <td class="xl64" align="right">6</td> <td class="xl79" align="right">112.1</td> <td class="xl79" align="right">60.6</td> </tr> </tbody></table>

 

[lenze]

Did you try the DoubleClick approach I posted? You could then dump the DV list altogether!! Plus, the code is a lot simpler!!

lenze

 

[Blieg]

My ability to handle VBA solutions is REALLY limited. I should be opening up Developer tab in Ribbon, macros - edit, VB opens, then paste the in a blank module sheet, save, and then how do I run it? Is there an FAQ for incorporating VB code I might follow?

I want simple!

Thanks Lenze.

 

[lenze]

Actually, you need to place the BeforeDoubleClick code in the worksheet module. Simply RightClick the sheet tab and choose "View Code". Paste the code in the white panel and close the VBE. As I wrote it, it works on Row 1, which I assume is your header row. If not, post back the details and we can adjust the code. We can also limit it to the 5 specific columns you want to sort by. Give it a try and post back.

lenze

 

[Blieg]

Yes yes yes yes awesome!

Works great! Thank you!

I will tell my boss Lenze was huge in solving this issue.