Advanced filter

[GodSpace ~ Catholic Books, Gifts & Music]

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

 

[Bob Greenblatt]

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.

 

[GodSpace ~ Catholic Books, Gifts & Music]

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

 

[Bob Greenblatt]

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.

 

[CyberTaz]

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.

 

[CyberTaz]

Yep - Works in 2004 the same way.

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

 

[Bob Greenblatt]

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.

 

[CyberTaz]

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.