Autofilter Excel 2007 vs Excel 2000

In using Autofilter on a Date column in Excel 2007 compatibility mode (Vista) and in Excel 2000 (XP), I receive different selection criteria (see pictures attached). In Excel 2007, I can select by year, by month. In Excel 2000, I am presented with all dates available in the filtered column.

I realize I can do a custom filter in Excel 2000 with a range of dates to select only the month I wish to query but this is cumbersome for the user. Is there VBA code available that will allow the same or similar selection criteria in Excel 2000 as is available in Excel 2007?

Thanks for your help. J

can you use a PivotTable? If the data is the correct type 7 laid out in a rable you can group dates by periods- months, quarters etc

http://excel-it.com/pivot_tables.htm

I'm currently using a pivot table to summarize all check register transactions by month, by category. The months are column headings and the categories are row headings such that I get total spending for each month by category. This pivot table then feeds another Budget worksheet so that budgeted spending is compared to actual spending (the source of actual spending being this pivot table).

A user said "it would be nice if I could "drill down" and see my actual spending detail for a specific category". I told her I would look at that possibility and have considered two approaches:

1. Give her access to the existing pivot table and allow her to double click on the category total for the month she wishes to query. This approach works and gives the user the information she's looking for. However, it creates another worksheet. So now we've got a bunch of extra worksheets hanging around and I've not figured out a way to automatically delete them when the user exits the newly created worksheet.

If I could write code into the newly created worksheet upon creation such that it "killed" itself when deactivated, this might be a viable approach. But, I don't know how to do this and I'm not real keen on the idea anyhow.

Is this approach even reasonable? Does it make any kind of sense to you? What issues will I be faced with down the road?

2. The second approach was to use Autofilter. I've written procedures to allow the user to turn Autofilter on and off so she can do queries to her hearts content. This works perfectly on a platform running Excel 2007 compatibility mode (Vista). She can query on any Category for any month and get the answers she wants.

However, running Excel 2000 (XP), the selection criteria is different. The month is not an available selection option, only the individual dates in the column are presented (see pictures with my original post).

Creating another pivot table could be an option if the user can query only on the category and month she wishes to see (say, through a user input form...enter the Category and Month...the pivot table s created). Is this even possible?

Thanks for your help and interest. J

I found this reference tonight about "Self-Cleaning Pivot Tables" http://msdn.microsoft.com/en-us/libr...spx#ListingOne
I've only played with it for a short while but it may address the issue of deleting extra sheets created by drilling down on a Pivot Table. I'd still like to find out if there is a way to make excel 2000 AutoFilter mimic Excel 2007 AutoFilter. Perhaps my children will get Excel 2007 for Christmas this year.
I'll let you know what I make out.

Thanks. J

At this point, I'm going with this model http://msdn.microsoft.com/en-us/libr...spx#ListingOne. I made a few modifications and it seems to be doing what it's designed to do.

I released the mods to my user (one of my children) this evening. We'll see if it fits her needs.

Thanks for your help. J