When I use an Advanced Filter in Excel, is there any way to set it up so
that, once the filter is defined, the filtered list changes as soon as I
change the criteria?

I want to automate the production of multiple lists depending on the
user's input, with transfers between multiple worksheets etc, and I
cannot ask the user to go through various worksheets and click through
Data--Filter--Advanced Filter--OK everywhere.

I could ask the user to click once somewhere. I suppose I have to write
a macro that will go through all the worksheets and execute
Data--Filter--Advanced Filet--OK everywhere? Somehow I feel there must
be a better way ...

Newt

You can use the Worksheet_Change event to update the filtered lists.
There are some examples on my web site:

http://www.contextures.com/excelfiles.html

Under the Filters heading, look at 'Product List by Category'

ms newt wrote:
When I use an Advanced Filter in Excel, is there any way to set it up so
that, once the filter is defined, the filtered list changes as soon as I
change the criteria?

I want to automate the production of multiple lists depending on the
user's input, with transfers between multiple worksheets etc, and I
cannot ask the user to go through various worksheets and click through
Data--Filter--Advanced Filter--OK everywhere.

I could ask the user to click once somewhere. I suppose I have to write
a macro that will go through all the worksheets and execute
Data--Filter--Advanced Filet--OK everywhere? Somehow I feel there must
be a better way ...


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra Dalgleish has some sample workbooks that depend on a click of a button to
separate data into separate sheets (using advanced filter).

You may want to see how she did it:
http://www.contextures.com/excelfiles.html

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items, creates
a
sheet for each item, then replaces old data with current. AdvFilterCity.xls 44
kb

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

And I don't recall snooping through this one, but it looks promising:

Filter Rows for Text String -- uses a Worksheet Change event, and
Advanced Filter, to display rows which contain a specifiec text string and/or
region. AdvFilterSearchWord.xls 31 kb


ms newt wrote:

When I use an Advanced Filter in Excel, is there any way to set it up so
that, once the filter is defined, the filtered list changes as soon as I
change the criteria?

I want to automate the production of multiple lists depending on the
user's input, with transfers between multiple worksheets etc, and I
cannot ask the user to go through various worksheets and click through
Data--Filter--Advanced Filter--OK everywhere.

I could ask the user to click once somewhere. I suppose I have to write
a macro that will go through all the worksheets and execute
Data--Filter--Advanced Filet--OK everywhere? Somehow I feel there must
be a better way ...

Newt

--

Dave Peterson