Ask Your Question

Calc recalc (AutoCalculate) broken if autofilter was active during formula creation [closed]

asked 2016-11-25 21:51:42 +0100

pmfrench gravatar image

updated 2020-08-05 14:45:48 +0100

Alex Kemp gravatar image

I'm using Calc to clean up a listing. I have autofilter turned on to select a subset of records, then enter a formula in an adjacent column outside the filtered list to help isolate problems in the list. I've found that the results from the formula don't auto update when I edit items in the filtered list in ways that should cause the results to update. Pressing F9 to force an update has no effect unless the cell cursor is on the cell containing the formula.

A simple example:

In cell A1 enter "Letters" In cell A2 enter "ab" In cell A3 enter "abc" In cell A4 enter "ab" In cell A5 enter "abc"

With the cursor in one of those cells, select DATA/AUTOFILTER from the menus. From the drop-down arrow in cell A1, deselect ALL and tick the box in front of "abc", then click the OK button. The list should now show only the "abc" entries.

In cell C3, enter the formula =LEN(A3) Copy the forumla down to the row below which should be row 5. The results for both formulas should be "3"

Now if you edit the contents of A3 or A5 to change the length of the string, the results of the formulas in column C should auto update, but do not.

Using version on Win 10 64bit installation. I tried it with version 5.2.x.x on a different machine and got similar, but not identical results. I believe that on that version the first edit to entries in column A did result in an updated result in column C, but subsequent edits in the filtered list in column A did not.

Other spreadsheets (Excel, SoftMaker's PlanMaker) do not have this issue.

(Subject edited slightly hoping to get it more clear by @Lupp )

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-05 14:46:28.033610


I cannot confirm this with V5.2.3.3 (portable, 32 bit) on Win 10. Reproducing your steps everything works as expected. With 'AutoCalculate' on the string lengths are updated immediarely.

Lupp gravatar imageLupp ( 2016-11-25 22:28:25 +0100 )edit

Using version

Please upgrade. Really.

6.2.8 and 6.3.3 are available.

erAck gravatar imageerAck ( 2019-11-21 14:34:50 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-11-20 19:01:53 +0100

newbie-02 gravatar image

updated 2020-07-24 07:58:36 +0100

calc defintely has a problem with autocalculate esp. when the sheet contains 'shared formulas' ... :-(

it's under work and nearly solved,

'F9' doesn't help in this situation, it's easily misunderstood, 'F9' only! recalculates intentionally 'unfixed' values like date, time or 'random', and cells depending on them?
[edit] -----------
and the actually seleted cell, 'the focus',
[/edit] -----------

to recalculate the whole sheet use 'Ctrl-Shift-F9', sometimes referred to as 'hard-recalc' or 'forced-recalc', that is! save reg. autocalc-fails since some versions.

hope problem will be finally fixed soon ... 6.4. is very good, as long as you work without 'threaded' or 'CL' mode, but has other shortcomings thus i cannot use it, does! do autocalc-fails, plenty when 'CL', many when 'threaded', very few when 'unthreaded.

[edit] -----------

@Lupp: repro, still with ver. in win7 x64 without openCL or threaded, see:


(autocalculate is! on)

@erAck: upgrading doesn't help in this case, is there a bug?
dev-ver 7.1 top cell not affected but cells in rows below,
rc-ver not affected with two rows, starting from three 'len' formulas below each other second to penultimate not updating,
looks like a flavour of our well beloved 'autocalc - shared formulas' problem again,

[/edit] -----------



edit flag offensive delete link more


Issue confirmed with V7.0.0.2RC.
Though AutoCalculate is enabled, the formula only updates after explicit respective order.
@newbie-02: Did you already create a bug report at ?
If not, you should do.
Please link to the bug here in the default format tdf#nnnnnn . The link is the created automatically.
The issue vanishes for a formula after a fake edit and doesn't come back after undo.

Lupp gravatar imageLupp ( 2020-07-24 10:23:46 +0100 )edit

After an additional test: The issue did not persist after a Save/Load cycle.
It may therefore be exerienced very rarely.

Lupp gravatar imageLupp ( 2020-07-24 10:41:12 +0100 )edit

file a bug - will do shortly, needs some time for clear description,
exerienced very rarely - there are few things i like more then random and rarely showing bugs ... :-( ... may be QA admins asking for reliable reproducibility with an unreliable program are among them ...

newbie-02 gravatar imagenewbie-02 ( 2020-07-24 18:15:52 +0100 )edit

bug filed: tdf#135125, pls. recheck and set to 'new', many thanks to @pmfrench for having spotted this issue!

newbie-02 gravatar imagenewbie-02 ( 2020-07-25 10:16:08 +0100 )edit

Question Tools

1 follower


Asked: 2016-11-25 21:51:42 +0100

Seen: 212 times

Last updated: Jul 24 '20