Can someone mark this is target "OfficeLater" and check status - I don't seem to
have permission?

Hi Niklas,

your's ?

FRank

*** Issue 40422 has been marked as a duplicate of this issue. ***

*** Issue 42881 has been marked as a duplicate of this issue. ***

(nooooooooo! so dissapointed that this "enhancement" didn't make it to 2.0 Calc
would be fantastic if this worked, and without it working I just don't use
autofilter at all as there's too much risk to losing the invisible data.)

Behaviour I would like to see is:

When autofilter is on, ONLY visible cells are modified/worked with by any
operation, be it select, delete, fill by dragging or pasting, copying applying
styles etc etc.

I've just realised I missed the 3 year birthday of my original Issue 2977 back
in January. 

I'm afraid I cannot live without autofilter and will not use one that destroys
my data without telling me. This issue and the seemingly beligerant attitude to
not accepting this is a serious flaw caused by either code or design, has made
me lose total confidence in Open Office :(

I cannot even find it in myself to use it or recommend it which I find very sad
as I feel MS Office needs a proper competitor.  I have even spent my own hard
earned on MS Office to use at home and OOo is not even installed at Home or Work
anymore. 

So unbelievable that this is even being considered.  This is a DEFECT.  I cannot
use open office until this is fixed.

What kind of politics or man power shortages are going on here?

I hope this Issue did Not last long enough as Issue 2977 which is 3 years 
old :) 
I hope it considered for OOo 2.0.1 if cannot to 2.0 as the time limit. 

A late "happy" 3 year birthday to jclutterbuck.

I am sad to say, that I have implemented OpenOffice in the entire 
organisation, without being aware of this very big problem, we are a company, 
with offices in 15 different countryes, and I was looking forward to OOo 2.0. 
But I have downloaded the latest Beta, and the main problem is still there. 
when dragging data, from one cell, down, it will overwrite every cell between 
the first and the last cell, regarding of it is hidden or not (autofilter).

The users, use autofilter every day, with critical data, and this DEFECT is 
unacceptable to us.

If this problem is not fixed (and it doesn't look like it will) I am sad to 
say, that we need to go back to MS Office....

I have used a lot of energy to implementing OOo, and I do not look forward to 
use at lest as much, to convert 300.000 documents back into word, exel and 
powerpoint.

Hi, again,
Just need to comment the new beta 2 (1.95).
The enhancement made to cell formatting and deletion of cells & contents that 
leaves filtered-out cells untouched works excellent.
Still looking forward of it including pasting and modifying.
Keep up the good work making OOo the only sensible choice.

*** Issue 48637 has been marked as a duplicate of this issue. ***

adding ftack, a robinson crusoe from 2977. 

as nmn says, dragging over hidden cells is the single most dangerous defect that
prevents effective use
 

Dear all,

I was looking over the AutoFilter issues and found out that this issue is
actually duplicate of 2977, which was closed in 2004. Since issue 33851 is now
"replacement" for this issue I will repost my comments on bug 2977 here.

This issue is not resolved in 1.9.113. I hope it will be resolved before 2.0 is
released and why is this bug an enhancement?

To reproduce bug you can do this:
Add in one column numbers : 1, 2, 2, 6, 7, 7, 2, 9, 8, 3
Use AutoFilter, Standard and say Column A > 2
It will hide rows 1, 2, 3 and 7.
Now paste 10 numbers (lets say 10 numbers of 11) over Filtered column.
Those pasted numbers will delete 7th number 2. Only the numbers that were first
on the Filtered list were not overwriten. So your "enhancement" should have
overwriten the first three numbers also if we want to be consistent in the
behaviour, isn't it?

*** Issue 52355 has been marked as a duplicate of this issue. ***

Please comment on the attached specification, then I will revise and submit this
to the calc project.

Created attachment 29490 [details]
proposed specification

Do you think that this behaviour could be optional (change it somewhere in the
options?)?
I agree with the specs, but be carefull during the implementation. Pasting cells
doesn't work the same (as in MS Excel) with Filtered and Hiden cells. Pasting
cells over the hiden columns (or rows) works the same as in Calc now, but
pasting cells over Filtered cells is not working the same.

Hi sparcmoz

Is it possible to target this for 2.0.2? I don't think this is an enhancement
that calls for new specs but a DEFECT implicit variant that just turned out to
be uncovered in the specs for issue 2977.

Søren

I have lost 3 hours, today, after a paste with auto-filter!
I completely agree that data should not be destroyed without notice.
If change is too complicated, please add a warning pop-up window asking for
confirmation when non visible data will be changed by Delete/Paste.

Don't hold your breath. I posted the original issue 2977 as a DEFECT almost
exact 4 years ago and its still not fixed.

I get the impression that the developers have never really understood how
Autofilters work in MS Excel and Lotus 1 2 3 (both of which I've personally
tested) and more importantly how most users use them. They therefore got the
design wrong in the first place and refused to accept it is wrong ever since. If
this is an unfair comment then why hasn't it stayed as a DEFECT and been fixed.

nope, jclutterbuck you are 100% correct.

This is an absolute showstopper for me, and I haven't used OO since I added
myself to this bug.

This issue and issue no. 25855 "Copy multiple seperated rows" are one that are I
am getting most complains from my users. We know they should fix this and they
know they should fix this. Don't they just have to check if cell has filtered
property set and then go to next cell with paste?

This bug could cause bigtime trouble for the French crime stats and finances:
http://www.theregister.co.uk/2006/02/06/french_police_ditch_ie/ Do you think
they are aware of it?

I think many people are unaware that deleting and formatting of filtered cells
was rectified in some version prior to 2.0. I have personally tested that.

However pasting is still to be rectified, which actually is the most important
aspect for me.

The filtering feature of OpenOffice is completely useless. There are so many
bugs, including pasting. I tried going through the source code for getting an
understanding. 

However it was surprising to note that there is no proper documentation within
the  code. No conventions seem to have been followed in naming the C++ files,
with names like documen1.cxx, documen2.cxx etc. Also, many of the comments, if
any are in German.

I thought that an international project like this was better managed by Sun

If you need help in understanding the source code, feel free to ask on the
dev@sc.openoffice.org mailing list.

But first steps first: There needs to be some understanding of what exactly the
new behavior should be, especially with regards to formulas and their
references. The attached specification doesn't say much about that either.

nn said:
>There needs to be some understanding of what exactly 
> the new behavior should be, especially with regards 
> to formulas and their references.

Thats easy.  Be like Excel.

"Like Excel" means, if the clipboard contains several cells, they are pasted as
a contiguous block, overwriting filtered-out cells. Judging from the comments
here, that's not what everyone wants.

I do not agree with the "Additional comments from nn Thu Mar 2 09:07:18 -0800 2006".

Like Excel means: Cells returned by filter should be considered like cells
selected with the mouse and CTRL.

That means: If A10 and A12 are selected by the filter, pasting two cells from
anywhere (either adjacent or not in the original location) will only change A10
and A12, completely ignoring A11. If three cells are pasted, an error message
should be issued (or the last cell should be pasted after all the other [hidden]
cells).

jeandom has hit the nail on the head.  You paste over the visible cells.

In some cases, Excel can't figure it out, and gives you the error "the cells you
are trying to paste do not match the pattern of the cells you are trying to
paste into"

*** Issue 63656 has been marked as a duplicate of this issue. ***

I had a problem similar to issue 21773, but then I came to know that it is
closed as duplicate of issue 2977. When I visited issue 2977, I came to know
that it is closed, and copied into a third issue 33851, which is supposed to be
a superset of issue 2977.

I see little resemblance in all 3 issues: In fact, the original statement of
this issue (issue 33851) is centered around Autofiltering. But there are other
ways of hiding rows (such as manual hiding or Consolidation of data); and this
problem would be applicable there also. 

But apparently this issue does not mention those cases.

The most generic description should have been "Unable to ignore contents of the
hidden cells during copy AND paste".

That would cover the effect of hidden cells in BOTH the source range and the
destination range.

****
Regading the handling of these issues, I have several observations:

Issue 2977 had 59 votes. Issue 33851 has only 37. 

Once a visitor votes for an issue, he wouldn't keep a watch on it, and if that
issue is copied into a new one (or closed as a duplicate), he is not likely to
transfer his vote to the new issue. IssueZilla certainly does not do this
automatically.

So, assuming that these votes are mutually exclsive, issue 33851 should have
59+37=96 votes as on today; making this one of the top-ranking issue. Against
that, we have a paltry 37 votes here!

So, when copying an age-old issue into a new one, we spoil its ranking by
splitting the votes.

That also results in faking the age of the issue: Rather than being >4 years,
the issue is now less than a year old!

To top it all, we pretend that it is an enhancement issue, rather than a defect.
That robs the remaining urgency out of the issue altogether, and allows us to
put away the issue in mothballs for a few more YEARS to come!

Could you repair (or enhance) this functionality. It is irritating lots of
people. It causes unpredicted way of insert/modify/ delete of cell - data
loss... Please fix is as soon as possible in 2.0.x line of OpenOffice.org.

Martin: Can you do anything to make the right solution earlier?

*** Issue 67659 has been marked as a duplicate of this issue. ***

*** Issue 67816 has been marked as a duplicate of this issue. ***

Hi friends. Does anybody know when will this issue be fixed ???
I agree that it is a major defect in the functionality.
I cannot get angry or bothered with the developers since after
all, Im getting the software for free.
But i would kindly ask for this issue to be taken seriously.

I guess it was overlooked that there already is an (at least partial) fix of 
this issue in issue 35582 - should fix cut & delete (or we have it in ooo-build 
for that purpose ;-) ).

The patch: http://www.go-oo.org/patches/src680/sc-no-delete-filteredrows.diff

Please have a look.  Thank you in advance!

Issue type: PATCH

Unfortunately, I have no clue on how to apply the patch.
Im working with OO for Windows at my workplace, I'm in
charge of helping with the move from MS Office to OO, I
know some programming but not that much.

ps. I think the priority for this issue should be P1.
ps. help us guys !! lets work on this issue

Kendy,

Yes, that patch may work (didn't verify) for row deletions and cuts (I'm unsure
what happens with those fragemented cuts if you paste them again though), but
does nothing to paste operations, which is the main problem what this issue is
about and why there is no progress, and so far no one came up with a
specification what exactly should happen under which circumstances, especially
regarding formula references. Setting type to FEATURE.

@phervas: you should read about issue priorities before talking about P1.
http://www.openoffice.org/scdocs/ddIssues_EnterModify.html#priority

The P's section says:

P3 (Default)
    P3 marks non-trivial problems which probably affect a noticeable number of
users. Issues with this priority must be fixed before the target release (see
Target milestone). Not fixing them for the target release must be justified by a
superordinate rule.

P2
    P2 marks severe problems which affect a significant number of customers.
Issues with this priority must be fixed before the target release (see Target
milestone), which usually is the next major release, and should be dealt with as
soon as possible. Not fixing them for the target release is not acceptable.

P1
    P1 marks extremely severe problems. Issues with this priority must be fixed
immediately, and the fix must be included in the next available build of the
application. Not fixing those issues is simply impossible.

Ok. This is my opinion with respect to this issue, people may argue of course.

It can't be P3, because it isn't a non-trivial problem, to me it is a severe
problem with the functionality. As some people have written, this defect can
result in the loss of data, and at my workplace it happened, that's how I came
to realize of this issue.
I would mark this issue as P1 just for how important it is to me (and I know to
others too), to me its a "extremely severe problem".
I don't mean disrespect, I can't demand anything from the developers, I can just
ask and suggest. I would also like to help, because I would like to have this
issue fixed for the good of all us users of OO.

What do you guys think ?

sincerely,

Patricio Hervas

Just couldn't agree more with you Patricio. Data loss in a common operation, this is just plain  
unacceptable. Period.  
  
OOo development team is getting so burocratic that is more and more like a government agency  
it seems, that it's not even able to do anything about such a serious problem in a two year time  
frame. Wow! That says something...!  
  
Sorry if I sound harsh, but we are already at 2.x and OOo still loads extremely slow, consumes a  
lot of resources, now from 2.x on it save medium to large documents unacceptably slow too, and  
it even causes loss of data...  
Time passes and I'm really getting dissapointed, when developers (or the ones in charge of telling  
them what to invest time on, more precisely) don't even seem to be able to detect what it really  
is a serious problem and what not.  
Sorry, but that's how it feels from this side.  
 

I will attach a little macro that I have used now for about 6 months, I never use a spreadsheeet without 
filters and need this macro to survive. This macro implementss the specification previously attached. It is 
slow and lacks features to UNDO and block user input, but it  demonstrates the required functionality that 
in my experience will meet 99% of the needs of power users who are the target here. Users already are 
allowed to create mayhem dragging functions around so that is a different issue. If this copy feature is 
adopted, then the corresponding drag action would be  a safety net. There could be a warning about drag 
over filtered cells similar to the existing warning for paste onto non-empty cells. 

Created attachment 38430 [details]
paste not onto filtered rows

I have just calculated that it is ONE THOUSAND SIX HUNDRED and FIFTY FOUR days
since I first posted this bug under Issue 2977.

This is a major DEFECT which destroys data without users being aware.

The OOo developers don't seem to be able to accept they got the design wrong. I
have raised it with the OOo Council to know avail. Perhaps someone should tell
Microsoft - I'm sure they will give it plenty of publicity, and maybe the OOo
team will wake up!

*** Issue 68350 has been marked as a duplicate of this issue. ***

"If someone deletes your data FOR FREE, you are not supposed to crib about it."
- OOo credo.

I have migrated my company to OOo (80+ people).
We are part of a much more larger company (1000+), our migration was a test 
drive of OOo.
After having unvoluntary issued a duplicate of this issue and discovered ist 
history I will stop the migration (three relevant datalosses in one day) as 
probably my data are as valuable as the money I will have to pay to Microsoft 
to update the existing version of their suite.
I can live with bugs but not with design decisions that compromise my activity 
and that are unconfortable to me. 
Let's wait for OOo 2.1 to see if something changes.
Anyway 1 vote more for this issue.
I indeed think that priority SHOULD BE P2 and type DEFECT.

-> Martin: Can you assign more resource to resolve (fully) this old and ugly
issue? Thanks,
KAMI

People.. my issue was duplicated to here.

This is a CRITICAL issue and needs to be fixed!
A very problematic usability problem!

[kendy] How can I recompile the OpenOffice.org to test your source?

Daniel FL

Thanks a lot sparcmoz  for the macro, it works great !!

It's good to see some action being generated with regards with this 
issue, hopefully it will get fixed soon.

Thanks,

Pato Hervas

Let's recapitulate some scenarios to show that yet it isn't even agreed
upon how the behavior should be:

1. Copy cells
   This sounds easy: just copy the cells that are not filtered out.
   - BUT: what to do on paste (on a non-filtered range) if the cells
     contain formulas with relative references? How to adjust?
     - Excel 2003 in this case doesn't paste the formulas, but only the
       values.

2. Cut cells
   Sounds the same as #1, just cut the cells that are not filtered out.
   Though the paste case may be easier, probably the references should
   be adjusted the same way as usual in a cut/paste scenario.
   - BUT: Excel 2003 in this case cuts _all_ cells, including filtered.

3. Paste cells
   Again sounds easy: just paste on the non-filtered rows.
   - BUT: what should happen with the gaps? Should the cells to be
     copied be skipped, or should they go in the next non-filtered row?
   - AGAIN: how to adjust relative references then?
   - Excel 2003 in this case pastes also to filtered rows if the cells
     were copied from a non-filtered range. It only does not paste to
     filtered rows if the cells were copied from the same filtered range
     _and_ rows. It does paste to filtered rows if the source rows were
     different.

4. Fill cells
   Sounds easy: fill only non-filtered rows.
   - Excel 2003 does that.

5. Fill Series
   Fill continuous in the non-filtered rows? Or fill with gaps, leaving
   out the values that would go into the filtered rows? Or do nothing?
   - Excel 2003 disables Fill Series on filtered ranges.

You see, especially from #2 and #3 the argument "do it like Excel" is at
least half-moot.

Do you want happy users and fast OOo adoption? 
Just do it like Excel does. Period. 
This will silence almost all user complaints and leaves almost nothing else against which to 
compare the functionality with... after all what else exist to compete with Excel and OOo? 
Let's be realists... 
 

6. Delete cells
   Delete only non-filtered rows.
   - Excel 2003 does that.

We discussed it with er and it looks Excel implementation is logical if we want
for example to delete, but why cut is different? Same good is copy, but paste is
might be parital solution in Excel and fill series does not usable at all. (I
also found a bug with cut because you can't undo it when you set back filtering
to show everything:) This applies to Excel 2k3 and it is corrected in Excel 2k7)
I think Excel support these thing at minimal level. Of course Calc does not so
we need more improvement, it is sure.
To conclude, my first idea was - do it like Excel. After a testing peroid with
Excel 2k3 and 2k7 beta I see the picture different. Of course we have to do some
things like Excel. These are:
* Copy (Currently it is ok in Calc)
* Delete (Must be fixed ASAP in Calc)
* Fill cells (Must be fixed ASAP in Calc)
But others might be better than excel
* Cut (Must be fixed in Calc and Excel) - It should work like Delete
* Paste (Excel is paste only values, is it enough to us or should is be better
like formula copy/"translation"? We have to discuss it.)
* Fill series (NA in Excel) Do we need it at all? Of course it can beat MS in
filtering theme :o)
Also what about to implement a button (generally a switch) that show and hide
the filtered data. It might make the life easier, does't it?
In my opinion we should slice this issue to parts and implement it one by one. I
think the delete should be the first (along with cut) - PATCH is provided by
ooo-build's guys but we have to test/review it. Then paste and fill at the end
fill series...
Thanks,
KAMI

As Eike (er) points out, to be "like Excel" is not a sufficient specification of the requirement. When 
people say "like Excel" this might be a considered view covering all scenarios, but in other cases the 
person only sees a small part of the behaviour and does not know about other things done by Excel. So 
"like Excel" is only a guide but it is not sufficient for definition of a requirement. 

I think it is useful first to consider how a filtered spreadsheet is used. It is different from other uses of a 
spreadsheet. The filter is a visual tool, that changes a view of the values, while the underlying cell 
contents including formulas are not changed. The filtered spreadsheet mainly has significance for the 
user looking at the values. Typical uses of a filter would be:
- view rows where a cell in some columns meets some criteria
- clean up data - change certain values (like re-code of data)
- fill cells with data according to some criteria
- extract (copy) values from rows that meet some criteria
Notice that the user in these cases is only concerned with values. In my experience any other uses, 
would be better done a different way (not using filters).

Also consider how other features are implemented when there is ambiguity about the user intention. 
Copy/paste is a good example. Does the user intend to copy the formula or the value? There are plenty 
of users that have no idea about this, and the feature defaults to paste the formulas. Should formulas 
be replicated as relative reference or absolute? What is the intention of the user? My point here is that 
ambiguity about features is common and does not prevent implementaation, it is only necessary that a 
decision is made. 

I would never recommend a user (either Excel or OOo) to adopt scenario 2,3 or 5, as I do not consider 
those to be safe or good practice, however I accept others may feel differently.  So I will comment only 
on 1 & 4. If anyone explains their reasons for using 2,3 or 5 I will try and suggest a different method.

Scenario 1 copy/paste - in my experience this is useful only for pasting values. I cannot imagine why 
anyone would like to paste formulas copied from a filtered range, or how the users intention could be 
described. The decision in Excel to paste values seems a reasonable solution, and would at least be an 
improvement over the current behaviour. I would be interested if anyone can explain the reasons for 
pasting formulas from a filtered copy.

Scenario 4 fill - for me this is the feature that I have used frequently in Excel and it is my "most wanted" 
for OOo. I think this feature alone would be large improvement. The requirement in this case seems 
clearly specified.

Regarding the Excel implementation, I would not search for logic there. My recollection is the same bug 
existed in early Excel filters and was fixed about 13 years ago, possibly some compromise was 
necessary. 

Kami,

> 6. Delete cells
>    Delete only non-filtered rows.
>    - Excel 2003 does that.

Also OOo does that, so I didn't mention it.

> * Copy (Currently it is ok in Calc)

I also think so. And also the reference handling within formulas is
acceptable, I think. So we can remove Copy from the list and remain with
Cut/Paste/Fill

> * Delete (Must be fixed ASAP in Calc)

As said, this _is_ fixed, don't remember right now exactly when it was
changed, some 2.x version.

> * Fill cells (Must be fixed ASAP in Calc)

I forgot to mention in my last comment above for section #4 that there
is also the case of relative references in formulas with Fill. So "must
be fixed" doesn't do, we need to specify what should happen and how to
adjust the references.

My suggestion: do it the same as if one single cell was copied to the
clipboard and then pasted to several locations, leaving out the hidden
rows. Most logical. And is also what Excel 2003 does.

> * Cut (Must be fixed in Calc and Excel) - It should work like Delete

Seconded, additionally keep in mind that cut cells will probably be
pasted somewhere else. As mentioned, relative references should be
adjusted as usual for this pattern. A second Paste then should follow
the same logic it already has nowadays when pasting a copied range.

> * Paste (Excel is paste only values,

Not true. See my section #3 above. Maybe you're confusing this with the
paste action after having copied cells from a filtered range.

> * Fill series (NA in Excel) Do we need it at all?

I don't think so. Applying a series fill onto a filtered range doesn't
make much sense, except if you want to enumerate a set of identical
values. That again would be a valid usage scenario.

> Also what about to implement a button (generally a switch) that show
> and hide the filtered data. It might make the life easier, does't it?

PLEASE do NOT introduce any new ideas or RFEs here that are not of the
topic discussed, life is already complicated enough..

> In my opinion we should slice this issue to parts and implement it one
> by one.

I also favoured that idea once, however, the reference adjustments that
have to be done in the case of cut/copy/paste actions are related
anyway, all actions that involve the clipboard need to be specified
together in their interaction. We could split off the Fill(Series), but
then again, would it be worth it? Fill is simply "copy one cell to
several places", and if Series is to be disabled, that's it. If we focus
on the specification I think we can do it in one draw.

> I think the delete should be the first (along with cut) - PATCH is
> provided by ooo-build's guys but we have to test/review it.

As the Delete is already implemented there would be only the Cut left.
Btw, that go-ooo patch seems to have some draw-backs and makes Calc
crash, see issue 68258.



Jim (sparcmoz),

> Scenario 1 copy/paste - [...] I would be interested if anyone can
> explain the reasons for pasting formulas from a filtered copy.

Well, usually you paste what you copied, and if you wanted result values
instead of formulas you could always use PasteSpecial respectively the
<Insert> key. I also do not think that it is of much use in this case
though. If we agree to do it different and specify as such, fine. Note
however that when pasting on the filtered range, the formulas should be
retained and adjusted, as the user probably wants to reuse them.

> Scenario 4 fill - [...] I think this feature alone would be large
> improvement. The requirement in this case seems clearly specified.

Seconded. Do you plead for splitting the Fill scenario off to a separate
issue so it could be implemented independently and maybe earlier?


It seems we're finally seeing some light at the end of the tunnel..

  Eike

Eike 
> when pasting on the filtered range, the formulas should be retained and adjusted,

My instinct is against this, I need think about what it means...

> Seconded. Do you plead for splitting the Fill scenario off to a separate issue 

Yes, I think separate issues will make further discussion easier to follow, thank you. 

Jim

The problem with the cut function is that neither OOo nor MsOffice can cut cells
from multiple selections. I was thinking on how complicated it would be to
implement cut with filtered cells. 
I was testing this and I found that in the OOo 2.03 version implemented in
"Ubuntu AMD 64" cut works well, but when pasting the cells, it will also paste
empty cells corresponding to the invisible cells, which i don't think is what we
would want. It is interesting that cut is semi "fixed" in the Ubuntu
implementation and that they have a 64 bit version of OOo.

*** Issue 69394 has been marked as a duplicate of this issue. ***

Dear developers,
what is the current status of this issue? Do you consider spec defined? What 
can non-developers do so that this isssue is resolved as soon as possible? 
Maybe in OOo 2.2?

In this issue's comments, there are different suggestions ranging from "change
only paste of a single cell/row" (as in the attachment) to "cut, fill, paste of
larger ranges must also be changed", with or without special handling for
formulas. Basically, nothing has been agreed on.

I'm adding mmp (from the User Experience team) to the cc list, maybe he has an
opinion. This issue has a lot of votes, so something has to be done.

At least two things are agreed - there is need to (a) prevent silent loss of data and (b) provide some 
features. The different suggestions are not mutually exclusive. In my opinion the paste/fill/drag from a 
single row/cell (as attached) would be a good first step but the decision how to proceed must depend on 
resources and code issues known only to the developer. 

It has been previously discussed and agreed that Cut/Copy and Paste should only
operate on selected (displayed) cells.
It is easy to understand, and equals to what normal users would think!
.
If rows A, B and D are displayed, only these rows are copied and rows C, E, F...
are ignored.
.
On another sheet, for instance, if rows K, L, P, Q and R are displayed
(filtered...), and row L is selected, then row A should be pasted on row L, row
B on row P and row D on row Q.
.

jeandom speaks sense to me! That's exactly what I'd expect.

An that should work the same way for dragging selected areas, eg. if rows are
filtered to show 1,3,5,6,7,8 and you drag 6,7,8 up three visible cells, then
their values should be moved to 1,3,5.

As for filling (ie. when you highlight a cell (or cells) and drag the handle
down) this should also only fill in the visible cells. However, formula should
be properly changed to count the hidden cels. 

eg. if cell B1 has the formula =A1+1 and row 2 is hidden then drag-filling down
to the visisble cell below (ie. B3) should set B3 = B1+1, but should leave B2
untouched.

*** Issue 29563 has been marked as a duplicate of this issue. ***

er -> Can you do something to implementation? These features are very important
to many users.

Folks, would you PLEASE understand that as long as there isn't a clear
specification there will be no implementation? Thank you.

Having to read confusing descriptions doesn't help much either:

> eg. if cell B1 has the formula =A1+1 and row 2 is hidden then
> drag-filling down to the visisble cell below (ie. B3) should set
> B3 = B1+1, but should leave B2 untouched.

I'd say B3 should be =A3+1 instead, but with this sample data that isn't
even related to the problem.

The interesting part would be if B2 contained =B1+1 and was filled to B3
to B5 where row 4 was hidden. I think B5 then should become =B4+1,
adapting the reference the usual way. Of course B4 should not be
touched.

> > B3 = B1+1, but should leave B2 untouched.
> I'd say B3 should be =A3+1 instead, but with this sample data that isn't
> even related to the problem.

Yes, oops *blush*, bad place for a typo -- I meant =A3+1 (!)

However, I argue that it **is** clearly related, because at the moment if you do
this, it fills (ie. silently overwrites) cell B2 with the formula A2+1. As I
point out, cell B2 should be untouched.

> The interesting part would be if B2 contained =B1+1 and was filled to B3
> to B5 where row 4 was hidden. I think B5 then should become =B4+1,
> adapting the reference the usual way. Of course B4 should not be
> touched.

Yes, I agree.

er -> Can we split this isuue to separated issues as we discussed before. It
might be easier to define specification, and work it out. How can I help your work.

er -> Can we split this isuue to separated issues as we discussed before. It
might be easier to define specification, and work it out. How can I help your work?

Dear all, can the text below be used as specification? 
Do you agree with it?

1.Copy cells:
Paste values – paste only into visible cells.
Paste formulas – paste into visible cells only, adjust formulas with relative 
references as if all cells were visible. Excel pastes formulas
2.Cut cells:
Cut values – cut only from visible cells, do not adjust formulas (as it is 
difficult to correctly guess user's intention). Excel cuts just visible cells.
3.Paste cells:
paste only into visible cells, treating them as single consequitive array. Do 
not adjust formulas. Excel in this case overwrites invisible cells
4.Fill cells:
fill only visible cells.
5.Fill series: 
Fill continuous in the visible cells, as it makes most sense.

I also would like to stress that it is not just filtering scenario and not 
only rows, but all cases where either row OR columns can be hidden (like 
hiding with Hide in right-click menu) need to be accounted for.

Any comments please? 

I agree, but am unclear what behaviour is described by:

"5.Fill series: Fill continuous in the visible cells, as it makes most sense."

I /think/ this means
a. don't overwrite hidden cells 
b. don't treat the visisble cells as a constituative (great word) array.

Erm. I find these things are v. difficult to describe clearly.

Non-filtered: A fill operation from a cell in row 1 containing the value 1,
dragging down the column to row 5 would put insert values 2,3,4,5 in rows
2,3,4,5 respectively.

If rows 3 and 4 were hidden, and you filled, then I'd expect row 2 to have the
value 2, row 3 and 4 to be untouched, and row 5 to have the value 5.

Is that what you meant?

peopleandplanet,
absolutely correct!!

Then I whole-heartedly agree, kpalagin!

--
Rich.

One more comment for this TERRIBLE issue !

I believed OpenOffice was a great alternative to MS leadership but discovering
this very old issue changed my mind !

There is no way working with that issue ! I don't want OpenOffice to be as Excel
but as many suggested, when several cells or lines are selected in filter
result, this selection MUST be considered as a DISCONTINUED selection (as if
selected using CTRL key) and everything should work fine !

That's it for that issue, but the great question is : 
How many other issues of that kind are still unknown of OpenOffice's adopters ?

I understand your frustration with this issue, voiced by me and many others.

However, I don't think you need worry about how many other big issues there are
unknown to adoptees of OOo, because the number is probably similar to the number
of huge issues with MS Excel, many of which are unknown until you've lost work too.

The difference is that OOo wants to improve and wants to listen to the rants of
the likes of me and you, and provides this mechanism for doing so where Excel
users just have to give up, fork out and hope blindly that the next version will
be better.

Ok, you're right ! I was just a little bit frustrated to discover that issue !

I fully agree with your specifications (peopleandplanet) and hope it will be
implemented ASAP !

Since no one is going to read and follow this overly complex issue anymore I
tried to summarize and started a specification draft in the wiki, please see
http://wiki.services.openoffice.org/wiki/Calc/Drafts/Issue_33851

Great idea to summarize our prefered issue !!!

Just another point that should be added, when using autofilter feature, and
selecting a criteria to filter lines, if you want to add another filter criteria
on another column, the list of values for that column includes hidden cells
values instead of just including values of filtered cells !

I hope my explanations are understandable ...

I'll add this point to wiki if others agree my point of view.

Sorry, but I do not agree with this point: The displayed values should only be
the possible choices, not all values.
To have the full range of values, we only have to remove criteria on other columns.
If we display all values when a criteria is already selected, we have no way to
know the limited choice we have with this criteria.
:-)
Jean-Dom.

btarrin, jeandom 
if I am not mistaken you started to discuss issue
http://www.openoffice.org/issues/show_bug.cgi?id=27745

Btarrin, please do not mix in other things, this is not a general
what-I-would-like-to-have-in-autofilters issue! Thanks.

*** Issue 72555 has been marked as a duplicate of this issue. ***

This one (33851) is STILL MARKED AS OOo LATER.
Will it be fixed in a "soon" version of OOo?
Thanks.

***

From 72555:

OpenOffice 2.0.4, Chinese (Traditional characters).
Windows XP Home SP2, Chinese (Traditional characters).

1. If you say this is technical correct, could you compare with the
   "Backspace" or "Delete" action?

2. Do row filtering, so that only row 2 and 4 are visible.

3. For any column,
   At row 2, drag the lower right corner to row 4 so that it will copy
   the content of row 2 to row 4.  Now row 3 is also affected.

4. ALTERNATIVELY, for any column,
   At row 2, do "copy". Then highlight both row 2 and
   row 4 and do "paste".  Now row 3 is also affected.

5. HOWEVER, For any column,
   If you select row 2 and row 4, and do either "backspace" or 
   "delete", it will not delete row 3.  (Same as Microsoft Office.)

6. Therefore, I personally think that if "5" is correct, then "3"
   and "4" are wrong.

Thanks.

Qiyao

------- Additional comments from zhongqiyao Wed Dec 13 21:14:38 -0800 2006 -------

I would also like to comment that showing a warning when the
user wants to paste does not really work, because:

1. The user may overwrite an empty cell.

2. The user may think that the paste is on non-empty cells
visible in the filter.

Thanks.

Qiyao

Yes, this is STILL targeted to OOoLater as there is STILL no specification how
the behavior should REALLY be and as long as there is none there will be NO
solution soon.

Please take a look at
http://wiki.services.openoffice.org/wiki/Calc/Drafts/Issue_33851 where we try to
draft a specification.

*** Issue 74544 has been marked as a duplicate of this issue. ***

The functionality described in the attached proposed spec and macro, for the
case of "fill down", appears to be implemented in OOo that I obtained recently
from debian/unstable (Sid) distribution. This is probably related to the patches
here http://www.go-oo.org/patches/src680/ for example, sc-filters-fill-fix.diff
Maybe someone can confirm that please? 

sparcmoz: Not sure about that patch exactly, but parts are really 
implemented/worked around - see the comment "Additional comments from kendy Wed 
Aug 2 13:07:03 +0000 2006".

added md to cc

looks like a data loss to me...
one can lose hours of work and valuable datas : primary issue 

Created attachment 45852 [details]
Extension for autoinstall of FilterCopy.bas (by sparcmoz)

I think this issue has got bogged down.

The "copy single cell - paste into multiple rows" is the most common usage
pattern. It is similar to the Database query "UPDATE xxx SET col = xxx WHERE
xxxxxx". This is the most common update - setting a value in multiple rows which
meet a certain criteria. And this is the feature I use the most in Excel -
filter the rows as per criteria and then add a column to either input by hand or
to paste a value.

You dont often see complex queries where you update multiple columns with values
selected from more than one column / row etc. 

Folks, may I please draw your attention to
http://wiki.services.openoffice.org/wiki/Calc/Drafts/Issue_33851
again, where we try to draft a specification?

No one is going to read the now 94 entries of this issue to digest some maybe
even contradicting feature wishes out of interspersed unrelated comments.

Thanks
  Eike

Simple to recreate. Make the following 3 row by 2 column spreadsheet
A   1
B   1
A   1

Auto Filter the first column then filter on the letter A
will now have
A   1
A   1

Click on the number 1 and change it to the number 9. Click on the number 1 in 
row 2 while holding the shift button down.

Now enter fill down
Should now have
A   9
A   9

Now Filter All and should have
A   9
B   1
A   9
BUT Instead the middle row is incorrect and get
A   9
B   9
A   9

*** Issue 81038 has been marked as a duplicate of this issue. ***

jeffbil: can you elaborate on this step:

"Click on the number 1 and change it to the number 9. Click on the number 1 in 
row 2 while holding the shift button down.  Now enter fill down."

I'm reading this as:

1. Select cell B1.
2. Type '9'.  DO NOT press ENTER.
3. Hold down SHIFT.
4. Select B3 with the Mouse.

Is this correct?  When I do this, I see that B1 and B3 are now selected, but
only B1 has the value '9' while B3 has the value '1' (i.e. unchanged).

And how would I "Fill Down"?  After Auto-Filtering on A, I only have two rows
visible -- row 1 and 3.  All other rows are hidden, so I can't drag the
bottom-right black square _anywhere_ except right (e.g. to fill column C), which
won't change B3 to have the value '9'.

What am I missing here?

Kohei brings enlightenment...

After filtering...

1. Select B1
2. change the value to 9, press ENTER
3. Select B1.
4. Press Ctrl+C.
5. Hold down SHIFT and select B3.
6. Press Ctrl+P.
7. Click "Yes" to "You are pasting data into cells that already contain data..."
8. Change AutoFilter to show all rows.
9. Note that B2 has value 9 when 1 is expected.

It's from 2004 that this bug is opened!!! and there are many many of 
signallings in merit...

When you mean to resolve it? It's a very very big limitation to use calc 
correctly...

Please Help poor customer :-(

Sorry for my very bad english

I am only downloading new versions of OoO because this bug makes the spreadsheet
unusable and I hope with every version of OoO that it is fixed. Now I realized
that OoO2.3 still has this bug and I found the history of this issue. Please
find a way to fix this bug: No one will ever use AutoFilter in a big sheet if
this bug is not fixed.

This is really a showstopper for a customer of mine. This issue, together with
the issues of the datapilot(not being able to change the range of a datapilot
for example). This should have been fixed a long time ago, and at this moment it
isn't yet assigned. So I hope that something is done about this soon. 

In short: Fill, Paste, Cut, and Move all overwrite filtered out rows. Copy, 
Delete contents, Delete row, Format, and Find & Replace in current selection 
don't.

*** Issue 85248 has been marked as a duplicate of this issue. ***

cc tbe

As there is no full conclusion drawn for all aspects, I'll tackle the most
important ones that are namely prevention of accidental data loss due to
overwrites / deletes in filtered rows, and uncontroversial behavior as far as it
can be implemented without having to dig over all multi-selection clipboard
functionality.

Accepted (starting).

*** Issue 86972 has been marked as a duplicate of this issue. ***

Targeting to OOo3.0

Short summary: implemented a strategy to not accidentally overwrite filtered
rows by treating a filtered selection like a multi-selection, which disables
all operations not possible on a multi-selection, like cut, fill, merge.
Exceptions respectively special treatment are:

- Deletion. Was already implemented to not delete filtered rows, no change
  necessary.
- Copy filtered to clipboard. Same behavior as before, range is copied to the
  clipboard including filtered rows, that when pasting or transfering to other
  applications are excluded.
- Paste from clipboard to filtered. Clipboard content is sliced and
  distributed over unfiltered rows.

I'll add details to the wiki draft page.

In cws filteredrows:

sc/inc/document.hxx  1.109.4.1
sc/source/core/data/document.cxx  1.86.4.1
sc/source/ui/app/inputwin.cxx  1.55.4.1
sc/source/ui/app/seltrans.cxx  1.13.308.1
sc/source/ui/drawfunc/fuins2.cxx  1.27.204.1
sc/source/ui/inc/viewdata.hxx  1.22.72.1
sc/source/ui/inc/viewutil.hxx  1.11.638.1
sc/source/ui/pagedlg/areasdlg.cxx  1.14.308.1
sc/source/ui/unoobj/viewuno.cxx  1.35.36.1
sc/source/ui/unoobj/viewuno.cxx  1.35.36.2
sc/source/ui/unoobj/viewuno.cxx  1.35.36.3
sc/source/ui/view/cellsh1.cxx  1.49.62.1
sc/source/ui/view/cellsh2.cxx  1.31.64.1
sc/source/ui/view/cellsh.cxx  1.45.64.1
sc/source/ui/view/cellsh.cxx  1.45.64.2
sc/source/ui/view/dbfunc3.cxx  1.16.34.1
sc/source/ui/view/dbfunc.cxx  1.13.308.1
sc/source/ui/view/formatsh.cxx  1.37.70.1
sc/source/ui/view/gridwin.cxx  1.92.4.1
sc/source/ui/view/tabview2.cxx  1.19.222.1
sc/source/ui/view/tabview3.cxx  1.65.34.1
sc/source/ui/view/tabvwsh4.cxx  1.73.4.1
sc/source/ui/view/tabvwsh4.cxx  1.73.4.2
sc/source/ui/view/tabvwsh8.cxx  1.5.470.1
sc/source/ui/view/tabvwshe.cxx  1.12.64.1
sc/source/ui/view/viewdata.cxx  1.63.64.1
sc/source/ui/view/viewdata.cxx  1.63.64.2
sc/source/ui/view/viewfun2.cxx  1.38.4.1
sc/source/ui/view/viewfun3.cxx  1.38.34.1
sc/source/ui/view/viewfun3.cxx  1.38.34.2
sc/source/ui/view/viewfun3.cxx  1.38.34.3
sc/source/ui/view/viewfunc.cxx  1.42.4.1
sc/source/ui/view/viewutil.cxx  1.16.4.1

Thank you very much!

Also added handling for drag&drop and primary X selection.
Reassigning to QA for verification.
Detailed description available at
https://tools.services.openoffice.org/EIS2/changesmails.EditFeature?Id=4208

Thanks Eike! 
Looking forward to 3.0Beta

Created attachment 52632 [details]
TestCaseSpecification

verified in internal build cws_filteredrows

Hi,
Just tried out the latest developer snapshot 3.0.0 Beta. The Paste Behavior has 
been corrected, but the Drag-fill behavior still is wrong. When I dragfill a 
selection that has some autofiltered rows, the operation overwrites all info in 
the hidden calls, and the value of my sequence is not as expected. Say I wanted 
the visible cells to have numbers 1, 2,3,4,5 and I have alternate rows filtered 
off, then I end up with sequence nos 1,3,5,7,9 in my cells instead.
I cannot believe this issue has been alive for 8 years or so. I know this is a 
community effort, but this ONE bug is keeping us hooked on to MSOffice.
Kar

Adding to my comments below:
If I try the same thing in Excel, it does not do a drag-fill, but does a copy 
to multi-selection. So If I mark two cells containing values 1,2 and then 
dragfill with mouse, the value 1 gets pasted to only the visible rows in the 
selection. Double-clicking on the Plus mark at Cell Right Bottom has no effect. 
Clearly different from Behavior whn there is no filter.
To me this behavior seems consistent. Even filling a sequence 1,2,3,4,5 in the 
visible cells would be consistent.
But updating the filtered rows with any value at all is simply not WYSISWYG.

I've just tried OpenOffice.org 3.0.0 Beta 300m10(Build:9296)
Paste behavior corrected, thanks!
But drag-fill still silently overwrites filtered data :-(
Please, fix this too.
Thank you!

The key thing is not to overwrite data _without warning_ so Paste is now safe
(thanks for that) but drag-fill is not. If the result of the drag-fill is
actually the same as without the filter, would anything be lost by simply
disabling a drag-fill which went over rows hidden by the filter?

I created issue 89232 as a follow-up issue for fill operations.

The autofilter for calc lacks the options found in Excel: Contains 
(especially), does not contain, begins with, does not begin with, end with, 
does not end with.

I have been using it for a long time and it is one of the main reasons I still 
need to use Excel.

johansteyn: There is already some work in progress on the additional filter
options, please see issue #35579#.

*** Issue 90862 has been marked as a duplicate of this issue. ***

*** Issue 90862 has been marked as a duplicate of this issue. ***

Greetings

Everyone needs to go and vote for issue 89232 as this has now stemmed off from 
this one and has little votes...

*** Issue 90285 has been marked as a duplicate of this issue. ***

*** Issue 85146 has been marked as a duplicate of this issue. ***

*** Issue 92707 has been marked as a duplicate of this issue. ***

closed because fix available in builds OOO300_m6 and DEV300_m31

But I have this problem in OOO 3m9. Replace works, but if I use filling by
draging + of cell, filtered rows fills to!!!

@babl - you need issue 89232

*** Issue 101011 has been marked as a duplicate of this issue. ***

Pls. see  Issue 101011 for an aspect not fixed with  Issue 33851!

No, see issue 89232 instead ;-)

This issue is closed. Please remove your votes to reopened this:
http://qa.openoffice.org/issues/show_bug.cgi?id=89232
Thank you!