« Things that don't work when you save Writer documents in Word's .doc format | Main | Using the Standard Filter »

November 12, 2007

Comments

Another very useful feature is to use autofilters to sum up data for only the visible data.

A slight gotcha there is that one has to use the SUBTOTAL function as opposed to the SUM function.

Hi Hawkse,

Good idea. However, I just tried it with SUM and it works fine; it only sums what is visible.

You bring up another point that I've been thinking of, though, which is that the AutoPilot feature combines filtering and calculations. I've done one entry for it and I think I'll be doing another.

Solveig

Operations which do NOT affect filtered out rows:
Copy
Delete contents
Delete row
Format
Find & Replace in current selection

Operations which DO affect filtered out rows:
Fill
Cut
Paste
Move

For way too much information (I hesitate to say clarification) on this extremely unintuitive behaviour, see:
http://www.openoffice.org/issues/show_bug.cgi?id=33851
http://wiki.services.openoffice.org/wiki/Calc/Drafts/Issue_33851
http://wiki.services.openoffice.org/wiki/Talk:Calc/Drafts/Issue_33851

Huw,

Thank you! It looks pretty inconsistent, doesn't it? Or as you say, not intuitive.

Thanks for the info, this will be very useful.

Solveig

Regarding AutoFilter, I have worked exactly it is mentioned above. I have calc files with 1000 rows and when I use Autofilters it displays the matched rows but below that, it also displays unmatched rows, for which I have not understood the reason. Kindly If you can suggest.

How do you apply auto filters on multiple sheets in a spreadsheet? If I set an auto filter on multiple columns on sheet 1 then go to sheet 2 and try to set auto filters on some of the columns on that sheet the auto filters on sheet 1 disappear.

The comments to this entry are closed.

GetOpenOffice Consulting

Get Book Resources

Search This Blog