Excel Advance Filter limitation problem???
Posted by Tom Urtis on March 10, 2001 1:16 AM
In an Excel 97 workbook I am trying to advance filter 5 range sets of data that can use the same criteria range. I placed all 5 range sets on one worksheet along with the criteria range (separated by enough rows & columns, etc) and wrote a VB procedure to filter all of them, but only one range set (the fifth one) got filtered. So, 2 questions:
(1) Is this a limitation of Excel, in that only one range per worksheet can be advance filtered, or am I missing something simple here?
(2) If there is such a limitation, then I would place each of the 5 data sets on its own worksheet and filter them individually (no big deal), but then would need to copy those 5 filtered subsets to a single worksheet and I am not sure how to do that either.
This would all be done in VB code.
Any thoughts or wisdom? Thanks in advance.
Tom Urtis
Posted by Mark W. on March 10, 2001 11:20 AM
Tom,
> (1) Is this a limitation of Excel, in that only
> one range per worksheet can be advance filtered,
> or am I missing something simple here?
One filtered list per worksheet appears to be the
case. Excel is doing more than just hiding rows.
You'll notice that the filtered, visible row numbers
become blue. Also, Excel's Copy/Delete command
behave differently on a filtered list. I suspect
that there's a modal aspect to filtering that prevents
the filtering of more than 1 list per worksheet.
> (2) If there is such a limitation, then I would
place each of the 5 data sets on its own worksheet
and filter them individually (no big deal), but
then would need to copy those 5 filtered subsets
to a single worksheet and I am not sure how to do that either.
You might what to take a look at...
and then...
Posted by Dave Hawley on March 10, 2001 3:27 PM
Hi Tom
I'm not too sure I understand you here. If you use Advanced Filter on say range A4:D20 with a criteria set for each Column, you will get the expected result. If you then Try to apply Advanced Filter to another range it will of course overide you previous filtred range, but what else could it do??
Confused
Dave
OzGrid Business Applications
Posted by Tom Urtis on March 10, 2001 4:47 PM
Dave --
Thanks for your message. As you say (and Mark W also noted) there is a limit of 1 filtered list per worksheet. Until now I quietly assumed that (for instance) criteria range A3:C4 could be used to filter data range A7:C30, and, elsewhere on the same worksheet, criteria range X100:Z101 should also be able to filter range X105:Z150. Without having thought about it before I wrote the original post, it seemed that more than one filtered list per worksheet should be do-able, given Excel's other capabilities. No problem though, I'm now using separate worksheets for each filter list, and have copy > pasted the 5 sets of filtered data to other places in the workbook where I wanted that filtered data to appear.
Thanks again to you and Mark.
Tom
Posted by Dave Hawley on March 10, 2001 6:40 PM
Tom, I must be missing something here!
>criteria range A3:C4 could be used to filter data range A7:C30, and, elsewhere on the same worksheet, criteria range X100:Z101 should also be able to filter range X105:Z150
It can! so long as all the headings are the same as your criteria range. But you have to realise that if you filter say A7:C30 first, then filter range D7:F100 any rows which do not match the criteria in your second filter (D7:F100) will be hidden and this will naturaly override any Visible rows in your first range (A7:C30). As I said, what else can it do? Advanced and Auto filter hide the ENTIRE row, not just the cell.
If your second range was BELOW your first then there is no conflict.
Dave
OzGrid Business Applications
Posted by Mark W. on March 12, 2001 7:39 AM
Dave, I tried and couldn't get Excel 97 to have
more than 1 filtered list per worksheet. By chance
is this now supported in 2000?
