Created attachment 11809 [details]
subtotal will not update

Hi Niklas,

I think autofiltered values should not be used in calculation if they are not
visible.

Frank

I did this building fix111 but i also used the updated the file
source/core/data/table3.cxx to version 1.15.164.1 to test the fix of the top ten
issue. Maybe this is not related.
Please note therefore I am not using any standard build version. 
The error was found by chance when I noticed subtotals did not update but I have
not been able to make another file with the error. The example file is just a
cut down version of the original. 

Due to time problems this is re-targeted to OOo2.0

When trying to figure out how this bug works, I tried typing in the 
spreadsheet.  It was in read only mode though and gave an error saying so.  
However, the error message would go away but the text box would stay and not 
refresh off the screen unless you manual do it by minimizing it and 
remaximizing it.  Or toggling between different windows.

System Specs:
XP Pro SP2
P4 3.0 GHz
1 GB RAM
60 GB harddrive
Open Office v.1.1.2

openoffice will not edit a "read only" file. If you save the file locally with a
with a new file name it should be OK. To observe the problem just select a value
in the filter button

A possible explanation for this bug: there is no database range defined in the
worksheet where the filter is defined.

I save this example in excel format demo.xls. then close and re-open the excel
file. now there is database range defined as Excel_builtin_filter_database_1.

I modified this range to include column C and the subtotal functions correctly.
Remove column c from the range again and the error occurs.

So maybe this is a feature - subtotal function does not update for data outside
the filter database range. 

Now it can be reproduced this way:

Step 1 - set up a filtered list.
Step 2 - add some data in a different column not included in the filtered list
range.
Step 3 - enter subtotal formula over both columns.

Only the subtotal over the original filtered range will update.

I can describe this problem better.

When a filtered list changes by a filtering action (different rows are selected
to be hidden) then recalculation is only applied to cells in the defined
filtered range, and cells that depend on those. Therefore a subotal of cells
outside the filter does not recalculate.

If calculation is forced after a filter change (ctrl-shift-F9) then the subotal
of cells outside the filter range is recalculated as expected.

So I am guessing the real problem is that the scope of recalculation is
restricted to the defined filter range and cells depending on that range, when
recalculation is triggered by a filtering action.  I believe this behaviour is a
bug, because users can do things that will require recalculation, outside the
assumed scope of recalculation. 

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

I'll post a $20 donation via PayPal as soon as this issue is fixed and available
for download. Trying to do my taxes, and the autofilter dynamic subtotals are a
showstopper for me. Thanks!

As a workaround, just change the filter range to include the column with the
values to sum.
The bug still needs to be fixed, of course.

Created attachment 46669 [details]
23299.patch

This change would modify the database range, just by opening the filter entry
list. That's not a good idea, it's too surprising. Also, it wouldn't work for
formulas that aren't directly adjacent to the filtered range.

My current thinking is: this is not really a defect, it is a feature because OOo
allows multiple filters in one sheet. A better solution would be the enhancement
issue that asks for a blue line to be shown around the filter range - I will try
and find that one ;)

Please have a look at issue 11870 and issue 14251

Created attachment 46912 [details]
23299.patch

Modifying ScTable::SetDirty would change the notification for all range
operations, not just the filtering. That's not a good idea.

Apart from that, the existing constants should be used instead of numbers like
255, and there could also be formulas to the left of the database range.

Do you think this change will lead to other problems?

Created attachment 46988 [details]
23299.patch

The fix (last patch from gaozm) is in CWS "calc44" (for OOo 2.4).

Type is "patch".

Verified the patch is in calc44 and the feature works as expected in the
attached test case file, and with formula in remote cells including all four
corners of the spreadsheet.

sparcmoz, thanks for checking, but let's follow the usual procedure of QA
verifying the issue on the final CWS build.

Setting "fixed" again.

Reassigning to QA for verification.

found fixed on cws calc44 using Solaris, Windows and Linux build

found integrated on master m239 using Linux, Solaris and Windows build