a little question about AutoFilter

Azen · Newbie Joined: 31 Oct 2006 Posts: 4

Well, I am new to this software and a very lazy person. I always use "Data-->Filter-->AutoFilter" to do the filtering. Sometimes I want to have autofilter on different sheets in a calc file, but I always find there is only one autofilter allowed in one calc file. If I put the autofilter on one sheet, the autofilter on the other sheet will disappear. Is there anyway to put autofilter on every sheets.

Warrick Nelson · Posted: Wed Nov 01, 2006 12:46 am Post subject:

I hadn't noticed this before - seems odd to me as Autofilter is a much easier way to filter than the Std filter, and gives a clear visual indication of a filter function in place.

You can have a std filter function on each sheet (or more than one on a sheet).

Azen · Newbie Joined: 31 Oct 2006 Posts: 4

Thanks for the suggestion.

I use autofilter because I can switch between different condition without much trouble. This minute I may need one kind of filter to find what I need, but next minute I may need different filter. I don't see how standard filter can do this for me like autofilter (such as the pull down menu to easily change the condition).

Hope this little bug will be fix in the next release.

Azen · Newbie Joined: 31 Oct 2006 Posts: 4

oh, one more little problem!

the Calc sometimes reports an error message, something about loading BASIC and general input/output. Could this be because I am using English version of OpenOffice, but I enter data in chinese?

Cutter · General User Joined: 31 Dec 2004 Posts: 10

I don't think so, I have the same problem wuth Autfilter but no error message and I don't use Chinese.

Villeroy · Posted: Wed Nov 01, 2006 2:25 pm Post subject:

Define explicit database-ranges for each list to be sorted, consolidated or filtered.
Data>Define...
Calc does all these operations in db-ranges. Whenever you filter, sort or consolidate without using an explicit db-range, Calc creates an invisible db-range named "unnamed". This jumps to any place where it is needed, so you can have only one db-range at a time. You can use the range-names in formulas just like "normal" range-names.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

Azen · Newbie Joined: 31 Oct 2006 Posts: 4

Villeroy:
You are saying I can only have one auto-filter because of the db-range name. If I define more than one db-range name, I can have more than one auto-filter in one Calc file.

maxqnz · Posted: Fri Nov 03, 2006 1:21 am Post subject:

Villeroy · Posted: Fri Nov 03, 2006 3:07 am Post subject:

One for the ghost hunters.

Hunt down hidden db-range "unnamed". It occurs multilingual ("unbenannt" in a german user interface), which makes things even more absurd and broken (why translate and hide at the same time?).
You have done any sorting, filtering or consolidation from selection.
If not: Select any range put any values in it (xxx Alt+Enter) and show auto-filters.
Call the name of the beast in exotic (non-english) countries:
Enable Tools>CellContents>Auto-Input and start typing into any cell =<yourGuess> <yourGuess> is "unnamed" translated to the language of your user-interface. The beast will echo for completion.
A column of formulas, starting at A1, calling the name of the english beast:
=CELL("filename";INDEX(unnamed;1;1))
=MID($A$1;FIND("#$";$A$1)+2;LEN($A$1))
=ROW(unnamed)
=COLUMN(unnamed)
=ROWS(unnamed)+A3-1
=COLUMNS(unnamed)+A4-1
=HYPERLINK(ADDRESS(A3;A4;1;"#"&A2)&":"&ADDRESS(A5;A6;1))

The last one is the compass, leading you to the cavern of the beast.
Put some values into another range and show auto-filters. The beast moves to another place, showing it's auto-filter-teeth.
Your hyper-compass did not change? Well, enforce a hard recalculation (Ctrl+Shift+F9). No luck? Well, enforce a "very hard recalculation" of the 4 row/col-formulae above: Apply F2, Space, Enter on each of them. Your hyper-compass leads you to the new cavern of the beast.
Knowing it's tactics of stealth, let's fight:
Select the cavern. Include neighboring rows or columns, just like a surgeon cuts into the healthy tissue around the cancer. Delete cells. Reset your hyper-compass and feel the shudder. It's still there, shrinked to a single cell -- squeaking.
Get an unused sheet and move the beast to this new empire. It stops squeaking, but it's still there, hopefully waiting for the things to come. This is your chance: Remove the entire sheet. Your hyper-compass gets invalid.

The beast is gone.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06