Advanced filter

Hey Bob - Here's a good one for ya, but I can't remember whether I tested it
in Mac XL or not... I know it works in PC version (2003)...

The "trick" is go to the destination sheet *before* Data>Filter>Advanced
Filter. You can use the Copy To: option and the Criteria Range can be on the
destination or the source wksht - makes no difference. What I haven't tried
is a Criteria Range on yet a 3rd sheet [Hold your Tickets, just tried it &
it works like a charm :)]

I'll try it in 2004 when I get home tonight, but I think I found it just as
cooperative there... just can't remember for sure.

--
Regards |:>)
Bob Jones
[MVP] Office:Mac

"Bob Greenblatt" <bob.RemoveThis@nospam.com> wrote in message
news:C2A6D339.95A29%bob@nospam.com...
> On 6/26/07 2:11 PM, in article C2A6A4D9.1356D%godspace3@verizon.net,
> "GodSpace ~ Catholic Books, Gifts & Music" <godspace3.RemoveThis@verizon.net> wrote:
>
>> 3000+ item list filtering for all items greater than 0 and using Advanced
>> Filter, telling it to copy to another sheet in the same workbook, by just
>> clicking on the sheet tab which fills in the Copy to: with "Inventory!"
>> (no
>> quotes. Then I get an error message that the formula contains an
>> "invalid
>> external reference to a worksheet". Got me - what is invalid about
>> another
>> worksheet in the same workbook - or does it want its own workbook??
>>
>> Claudia
>> Ibook G4 1.33 GHz
>> 768 M RAM
>> 55G HD
>>
>> IMac G5 1.9 GHz
>> 1 GB RAM
>> 150G HD
>>
>> OSX 10.4.10
>> Office 04 11.3.3
>>
> I see a couple of problems. When you click on the sheet tab to go to the
> other sheet, you must then click on a cell in that sheet to create a valid
> destination. However, if you manage to do this correctly you will get
> another error that says that you can not filter to another sheet. This
> isn't
> quite true as you can do it with VBA. However, without VBAS you are forced
> to do the advanced filet to the same worksheet. You could put it someplace
> out of the way, and then copy it to your inventory sheet.
>
> --
> Bob Greenblatt [MVP], Macintosh
> bobgreenblattATmsnDOTcom
>
 >> Stay informed about: Advanced filter 

On 6/26/07 6:06 PM, in article elUcmQDuHHA.668.TakeThisOut@TK2MSFTNGP05.phx.gbl,
"CyberTaz" <typegeneraltaz1ATcomcastdotnet> wrote:

> Hey Bob - Here's a good one for ya, but I can't remember whether I tested it
> in Mac XL or not... I know it works in PC version (2003)...
>
> The "trick" is go to the destination sheet *before* Data>Filter>Advanced
> Filter. You can use the Copy To: option and the Criteria Range can be on the
> destination or the source wksht - makes no difference. What I haven't tried
> is a Criteria Range on yet a 3rd sheet [Hold your Tickets, just tried it &
> it works like a charm :)]
>
> I'll try it in 2004 when I get home tonight, but I think I found it just as
> cooperative there... just can't remember for sure.
Nice try, but that still does not work in excel 2004. I know you can put the
filtered list anywhere using VBA, but, unfortunately not through the UI.

--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom
 >> Stay informed about: Advanced filter 

On 6/26/07 11:29 AM, in article C2A6D339.95A29%bob@nospam.com, "Bob
Greenblatt" <bob RemoveThis @nospam.com> wrote:
> I see a couple of problems. When you click on the sheet tab to go to the
> other sheet, you must then click on a cell in that sheet to create a valid
> destination. However, if you manage to do this correctly you will get
> another error that says that you can not filter to another sheet. This isn't
> quite true as you can do it with VBA. However, without VBAS you are forced
> to do the advanced filet to the same worksheet. You could put it someplace
> out of the way, and then copy it to your inventory sheet.
Great workaround Bob - thank you!

Claudia
Ibook G4 1.33 GHz
768 M RAM
55G HD

IMac G5 1.9 GHz
1 GB RAM
150G HD

OSX 10.4.10
Office 04 11.3.3
 >> Stay informed about: Advanced filter 

Yep - Works in 2004 the same way.

Regards |:>)
Bob Jones
[MVP] Office:Mac



On 6/26/07 6:06 PM, in article elUcmQDuHHA.668.TakeThisOut@TK2MSFTNGP05.phx.gbl,
"CyberTaz" <typegeneraltaz1ATcomcastdotnet> wrote:

> Hey Bob - Here's a good one for ya, but I can't remember whether I tested it
> in Mac XL or not... I know it works in PC version (2003)...
>
> The "trick" is go to the destination sheet *before* Data>Filter>Advanced
> Filter. You can use the Copy To: option and the Criteria Range can be on the
> destination or the source wksht - makes no difference. What I haven't tried
> is a Criteria Range on yet a 3rd sheet [Hold your Tickets, just tried it &
> it works like a charm :)]
>
> I'll try it in 2004 when I get home tonight, but I think I found it just as
> cooperative there... just can't remember for sure.
 >> Stay informed about: Advanced filter 

On 6/26/07 11:24 PM, in article C2A750A6.268D5%onlygeneraltaz1@com.cast.net,
"CyberTaz" <onlygeneraltaz1 RemoveThis @com.cast.net> wrote:

> Yep - Works in 2004 the same way.
Yes, I agree. I don't know what I was doing yesterday when it didn't work.
But following your instructions it worked fine in Excel 2004 today.

--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom
 >> Stay informed about: Advanced filter 

"Bob Greenblatt" <bob.RemoveThis@nospam.com> wrote in message
news:C2A7D44B.95AA9%bob@nospam.com...
> On 6/26/07 11:24 PM, in article
> C2A750A6.268D5%onlygeneraltaz1@com.cast.net,
> "CyberTaz" <onlygeneraltaz1.RemoveThis@com.cast.net> wrote:
>
>> Yep - Works in 2004 the same way.
> Yes, I agree. I don't know what I was doing yesterday when it didn't work.

My guess is that you were launching the Advanced Filter _before_ going to
the destination sheet. The only way I've found to make it work is to be in
the destination sheet *first*, then launch the AdvF - if you aren't there
when the dialog comes up you're toast:)

Once the dialog is open you can even mouse back to the source range & select
it rather than typing the cell references.

> But following your instructions it worked fine in Excel 2004 today.

It dawned on me one day when I actually took the time to *read* the alert
that pops up when you specify a different sheet than the one the list is on:

"You can only copy filtered data to the *active* sheet."

Most folks just have a tendency to start with the soure sheet as the
*active* sheet.
--
Regards |:>)
Bob Jones
[MVP] Office:Mac
 >> Stay informed about: Advanced filter