Advanced Filter Help

[Scott24x7]

All,
Is it possible to have an advanced filter and a AutoFilter applied to the same sheet at the same time? I want to advance filter a range, say A5:L1000 based on date = to current month in E (date field). Then I want to "Auto Filter" all the results so that I can show things like product, sales, person, and industry using the drop-downs in Auto filter. Is this possible?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[mintjulep]

Why don't you try it and see?

 

[Gavona]

I assume that you have discovered by now that you can't.

Not sure from your post why you need the advanced filter at all. However assuming you do:

Options include:
Advance filter to a new location then apply autofilter to the list you have extracted.

Repeat all relevant column headings in your advanced filter criteria (product, sales etc).
Extract a list of unique values for each of these headings using advanced filter. Then use this data as a data validation list. Thus creating dropdowns.
Now write a simple macro to apply advanced filter.
Possibly use events to trigger the macro whenever a value in the criteria range changes.




Gavin

 

[Scott24x7]

Gavin,
Thanks. Hadn't thought about copy to a new sheet and apply... The reason I need this is I have a monthly sales report that at the top has a "summary", and below all the data. I was wanting to filter the data by month, and then have "auto filters" on each of the columns so within the month its quick and easy for my user to just click on one sales person, or one product to see how it is doing within that month. They are not overly savvy users (sales people...) so I am trying to creat as many "click here" type functions. The driver is a drop down of Months (so they just select February or June or whatever) to see how that month performed.
Is there some better approach to this?

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[mintjulep]

How about MSQuery to a new sheet and then apply your autofilters?

I haven't used msquery much, so I'm not very familiar with it, but it seems quite powerful. I think you could drive the query through some drop downs or buttons on a sheet.

 

[Gavona]

Not sure how MSQuery would be an improvement on Advancedfilter to extract to a new sheet - but like mintjulep haven't used it much. If however you need to summarise the data (aggregate your sales data) then MSQuery would be the way.

I still don't understand why you can't apply an autofilter to select the month and users apply further filters. You might want a button on the sheet to "Reset Filters"

i had some success with the second approach I tried to outline in my earlier post. It uses VBA so this is not the right forum but users just select the criteria (product, month) from dropdown list and and a filtered list appears below the filter criteria they have entered.

The most verstatile solution might well be a pivottable. User selects Month, Industry, Product etc from a page field dropdown. Well worth exploring yourself as a way of producing high level summaries for them even if you decide they won't want to cope with the tool themselves.

You can create a row field but hide the detail of that field until the user double clicks on the field above. you can double click on a figure in the data area and the underlying data (Sales for productA in month3, say) is extracted to a new sheet (and can be explored with autofilter).
If you were going with this approach you would need to give users some training and you might want to make their lives easier by, for example, telling them how to add the Autofilter and Showall buttons to their toolbar.
Other features to be aware of:
Ability to group by month, year etc if you have a date field in your data
Field options, layout, outline view
Selecting all the totals for a particular row field to apply a different format to them.
Selecting just the headings to apply wrap text or a specific format to them.
The ability to hide specific data (I am not really very keen on this because it is not obvious that you have done that but can be useful).
This is really impressive: Produce a pivottable with Product as a page field. Sort out the formatting etc until you are happy that you get results you like by simply selecting a product from the drop down pagefield list. Now use the ShowPages command and in seconds a separate sheet is created for every product. (You may then need to select all sheets and fine-tune the column widths.)

Gavin

 

[SkipVought]

MS Query is definitely one of the tools in my toolbox for such an application.

After ADDING a querytable to a sheet, turn on your macro recorder and record EDITING the query and RETURNING data to Excel. Then use a combo selection, for instance, to supply a criteria value in the SQL code.

Then run the marco from the combo click event.

Pretty simple.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}