Excel - Saved Filters

I have an excel workbook, and would like to pre-define filter specifications using the filter drop/down menus, and then load in data that would automatically filter based on this, without using VBA.

I did a test myself, and did not get this to work, but wanted to see if this is possible.

Basically, I will be filtering repeated datasets using the same filter specifications, and would rather have these filters already defined.

Thank you.
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I don't think that you can without VBA. To clear the data for a new dataset will require removing the filters

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exp vgAuthor Commented:
Thank you.
Roy CoxGroup Finance ManagerCommented:
Glad to help. If you want to use code post back
Pierre HubautCommented:
Hello exp vg,

The import of data from external text files datasets differ significantly from import of data from other sources and, specifically, because Excel does not create a so-called "Excel Table" for the imported data range.
Your are then perfectly right, any refresh of a text file connection will cause the filters to be reset.
However, a refresh on other data connections (such as MS-SQL, MSQ-Access or MS-Excel as illustrated further) does not causse this issue provided the option "preserve column sort/filter/layout" has been selected on the data range properties ("Data" menu/ribbon, "Properties" - this option is unfortunately grayed out for text files connections).

In other words, if you can manage to have your datasets as Excel files, you may be able to achieve what you want.

There is still one issue with these other sources:
Selecting a different dataset with text files connection is easy because the system will prompt you for the file whenever you refresh (and assuming that the "Prompt for file name on refresh" data range property is set which is the default).
This is not (or usually not) the case with the other sources.

To get around this (I am now assuming you can get your datasets in Excel format), you can have an external command that will copy any of your dataset you want to process to a work dataset with a known constant name and use this one for import.
To process another dataset, just copy it to the work dataset with the known constant name and refresh your data import.
This is all illustrated in the attached files (created from C:\Test and as some absolute path are involved, please download there as well):
- A Master.xlsx Excel workbook with
  . Sheet1 importing the Text datasets illustrating the issue, if you refresh, you can select the "Data-A.txt" or "Data-B.txt"
  . Sheet2 similar but with a Excel import from a unique Data.xlsx file (you'll immediately see that the range is an Excel table)
    + You can then use the "Select-Data-A.cmd" command to copy "Data-A.xlsx" on "Data.xlsx" (or "Select-Data-B.cmd" for "Data-B.xlsx")
    + After reresh, you'll see either dataset A or B with sort rules, filters preserved
It is far from perfect and having a Excel Table import of Text files would greatly help but I have not seen this so far
(although in the past, Text files could be imported through the Excel ODBC driver - this disappeared)

Hope it helps,
best regards,
Roy CoxGroup Finance ManagerCommented:
It occurs to me that setting up a template file for Advanced Filter instead of AutoFilter5 might be an option.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.