Share this content
0
6
5488

Excel help - sort dropdown list in pivot table

I have a pivot table in Excel 2003, which contains a dropdown list in the table header that is based on a column of dates.

If the underlying table of dates is first populated out of order then the drop down list is presented in the order in which data is physically entered, which is not desired, rather than (preferred) in date order.  Subsequently re-sorting the underlying data table by date does not alter the sort order in the drop down list in the Pivot table.

I already have a little event-driven VBA routine which causes the Pivot table to auto-refresh whenever I activate the worksheet containing the Pivot table.  It would be helpful to include in that routine some code that causes the dropdown list to be sorted.  Any takers?

In fact, if that is possible, I would really want it sorted in reverse date order (latest date at the top of the drop down list).

While on the subject, could someone tell me whether later versions of Excel auto-sort these fields (and whether you can reverse sort them without resorting to VBA?)

With kind regards

Clint Westwood

Replies

Please login or register to join the discussion.

Either replacement or bubble sort ..

replacement - http://www.dfstudios.co.uk/articles/sort-algorithms-part-2/

bubble - http://www.sorting-algorithms.com/bubble-sort

Also seem to recall binary chop (from 20+ years ago as COBOL programmer) as very efficient way of searching

http://mines.lumpylumpy.com/Electronics/Computers/Software/Cpp/Algorithms/Find/BinaryChop.php

Thanks (0)

It is not the algorithm that is required.

Thanks, but the mechanism to sort the list is not required.  I am pretty sure that this can be handled in the background by way of a simple VBA command along the lines of

Application.ThisWorkbook.MyArray.Sort Order1:=xlDescending

Whether on compilation the VBA interpreter sorts it using a bubble sort or any other method is a matter of singular disinterest to me.  There will never be enough entries to sort for the difference to be noticeable, and even if it were noticeable I would be happy with the slowest method (ie any method).

No, the problem, here, is: how do I determine the name or reference or definition of the MyArray to be sorted?

With kind regards

Clint Westwood

Thanks (0)

Position the cursor in the field that you are selecting

Not sure if this is quite what you are looking for (and I aven't got any handy pivot tables on my home laptop) but -

 

I find that the sort order in the pivot table filter box varies depending on whether I have selected a cell in the pivot table that is within the field I am searching.

Ie if the fields are invoice number then value:

Click in the invioce number field, and I can then see invoice numbers in ascending order in the sort box

Click in the value field, and I can then see values in ascending order in the select box

 

Tom

Thanks (0)

VBA appalling ...

Forgotten just how appalling VBA is when writing code - anyway perhaps this may help

PivotTables is a collection within which a PivotTable is located and they are referenced as follows:

Sub PivotTest()

    Dim oPivotTable As PivotTable
   
    Dim sTableName As String
    Dim sTableValue As String
   
    Dim sFieldName As String
    Dim sFieldValue As String
   
    Dim iCountTables As Integer
    Dim iLoop As Integer
   
    iCount = 0
   
    ' cycle all PivotTables in collection
    For Each oPivotTable In Worksheets("Sheet4").PivotTables
   
        sTableName = oPivotTable.Name
        sFieldValue = oPivotTable.Value
       
        iCountTables = iCountTables + 1
       
        ' extract PivotFields in PivotTable
        For iLoop = 1 To oPivotTable.PivotFields.Count
            sFieldName = oPivotTable.PivotFields(iLoop).Name
            sFieldValue = oPivotTable.PivotFields(iLoop).Value
        Next
   
    Next
   
    Dim iShowCount As Integer
    iShowCount = iCount

End Sub

Thanks (0)

Excel help - sort dropdown list in pivot table

The solution I use is to remove the field from the pivot table, refresh the pivot table, put the field back into the pivot table and refresh. The dropdown list should then be in order.

Mike

Thanks (0)

But not ...

... in reverse order, which would really be preferred (although simple ascending order is better than at present).

Thanks, I will take a look at these suggestions over the weekend.

However I am pretty sure that I have tried the last suggestion and it did not work for me.  The original entry was "sticky" in some cache or other, and simply deleting and refreshing never got rid of it. May have another go.

With kind regards

Clint Westwood

Thanks (0)