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

 

Comments
There are 6 comments. Login or register to view them.

Either replacement or bubble sort ..

JC |

It is not the algorithm that is required.

nogammonsinanundoubledgame |
nogammonsinanundoubledgame's picture

Position the cursor in the field that you are selecting

tom123 |

VBA appalling ...

JC |

Excel help - sort dropdown list in pivot table

Mike Stonebridge |

But not ...

nogammonsinanundoubledgame |
nogammonsinanundoubledgame's picture