« Free Linux Ebooks | Main | Getting OpenOffice files into Google Web Apps, and getting your files back out of Google Web Apps »

January 09, 2008

How Various AutoFilter Operations Work on Data Not Currently Shown in OpenOffice Calc

I could have sworn I had posted this already, but I think it was just included in a comment on this post.

http://openoffice.blogs.com/openoffice/2007/11/using-the-autof.html

Thanks to Huw for the tips. For information on the sites, 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

 

Let's say you've got a big list of employees: name, address, etc. When you apply the filter and view, for instance, only people from Montana, some of the data isn’t shown. If you then copy, paste, delete, or perform other operations on the data, what happens to the data that isn’t shown? If you delete Artie Anderson from Montana and Cindy Chalmers from Montana, what happens to Betsy Bates from Nevada?

It all depends on the operation. Some, like delete, leave the unshown data alone. Some do affect the unshown data.

Operations that DO affect filtered out rows.

  • Cut and Paste

  • Move (dragging)

  • Fille (Edit > Fill or dragging)

Operations which do NOT affect filtered out rows:

  • Copy

  • Delete contents

  • Delete row

  • Format

  • Find & Replace in current selection

More About Operations that DO Affect Filtered-Out Rows

Cut and Paste, Versus Copy and Paste

With Cut and Paste, the non-shown data is cut and also pasted.

  • When you paste the data outside of the filter range, the whole set of data is pasted and unaffected anymore by the filter.

  • When you paste the data inside the filter range, the whole set of data is pasted, and all data is shown at first, even data that shouldn’t show for the current filter selections. However, if you re-apply the filter selections using the dropdown lists in the heading row, then the data is filtered correctly.

Click the following to see a bigger image.

Af1

With Copy and Paste, the behavior is different. If you cut, you get the nonshown rows, but if  you copy, you don’t.

Dragging Cells to Move Them

When you move rows (dragging) that include unshown rows, the behavior is the same as cut and paste. The unshown rows between shown rows are moved along with the shown rows.

Fill (Edit > Fill or Dragging the Cell Handle)

Here’s what happens with Fill. Here’s some sample data, and currently everyone is in the same department.

Af2

You now look at only people from Colorado.

Af3

You change the department for the first person from Colorado, and drag that department down through all the other people from Colorado.

Af4

Now, all the nonshown rows after the first row you changed are affected, but not the rows before that.

Af5

More About Operations that Don’t Affect Filtered-Out Rows

Copying and pasting, deleting, formatting, and Find and Replace don’t affect unshown data. The following section provides an example.

Deleting

Here’s a walkthrough of deleting rows while the filter is on. Rows that aren’t shown aren’t affected.

Look at the range from row 15, Dan Montbatten, to row 20, Beth Jerlin. Dan and Beth are both from Montana.  In between are Jon, Marcus, and Kyle.

Af6

The next illustration shows an AutoFilter with only people from Montana, which includes Dan and Beth but excludes the three rows between.

Af7

 

Now delete Dan and Beth.

Af8

And they go away. However, when the state autofilter criterion is removed, Jon, Marcus, and Kyle are still there.

Af9

 

 


Traininglogo



Posted at 11:51 AM in Calc, Calc: 2008 |

Comments

Hello,
anyboday know this, why we are not able to filter simultaneously. if i am doing filter in one sheet & trying to do with somedata in second sheet then first sheet filter removes automatically.

Regards

Posted by: rekha | June 11, 2008 at 05:48 AM

I have also noted this problem with the auto filter on a worksheet. Does anyone know if this has been reported/fixed in latest version 3.0.

Regards

Posted by: Kitwe | July 19, 2008 at 07:23 AM

Hi Rekha, Kitwe,

The arrows do disappear on the first sheet but the filter is still there. Click somewhere in the range of data on the first sheet and choose Data > Filter > AutoFilter and you see the arrows again. Which is annoying. I don't have access to my 3.0 beta OOo version right now so not sure if it's been fixed.

Posted by: Solveig | July 19, 2008 at 11:06 AM

Solveig,
Thanks for the information, it works great.
I have tested this in 2.4 and the Beta release for Version 3.0 and the same problem occurs. Will have to see if it has been logged.

Thanks.

Posted by: Kitwe | August 02, 2008 at 04:41 AM

The comments to this entry are closed.

GetOpenOffice Consulting

Get Book Resources

Search This Blog


Categories