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.

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.

You now look at only people from Colorado.

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

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

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.

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

 

Now delete Dan and Beth.

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

 

 

---

---

Comments

[rekha]

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

[Kitwe]

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

[Solveig]

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.

[Kitwe]

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.

[wow power leveling]

http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/

[wow power leveling]

http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/