Array formula won't update correctly (not even with F9)

columns: A B C D E
row1 Item Value Lookup Count Value

row2 C 23 C 3 23

row3 X 34 - - 56

row4 C 56 - - 67

row5 C 67

row6 X 78

Defined Name ranges:

A2:A6 = Item

B:B = Value

D2: =COUNTIF(item,C2)

Array Formulae

E2: {=IF(ROWS(E$2:E2)>D$2,"",INDEX(value,SMALL(IF(item=C$2,ROW(item)),ROWS(E$2:E2))))}

E3: {=IF(ROWS(E$2:E3)>D$2,"",INDEX(value,SMALL(IF(item=C$2,ROW(item)),ROWS(E$2:E3))))}

E4: {=IF(ROWS(E$2:E4)>D$2,"",INDEX(value,SMALL(IF(item=C$2,ROW(item)),ROWS(E$2:E4)))) }

Sheet as above is calculating correctly.
Change C2 from “C” to “X” - the correct values will be displayed in E2 and E3 (34, 78).
Change C2 back to “C” - now only E2 and E3 are populated correctly. E4 is blank, instead of having “67”.
See new screenshot below:

If you push F9 - nothing happens.
The only way to recalculate correctly, is to recopy formula E2 down E2:E4. then the correct three values are displayed (23, 45, 56)

LibreOffice version:

OK, it seems to be a bug. From
"I tested your sheet with three versions of LibO

The first 2 of these dont’ show the behaviour you describe. The results are recalculated reversibly. The third one shows the bug."

I have a similar problem - a simple formula doesn’t recalculate after a macro operation. I have posted an .ods file that demonstrates the bug at:

The failure to recalculate bug happens in LO, but works fine in LO

I’m glad that I hold myself from upgrading to LO version 4.1.x. Still happy with version 4.0.6. I will wait until version is 4.1.5 or higher.

I think arrays needs a hard recalc [Shift+Ctrl+F9].

‘hard recalc’ (forced recalc, ctrl-shift-F9) isn’t a fix for broken functionality!
it’s an analysis tool to check if something went wrong with the calculation chains,
the more we start using it as standard, the more the users get prone to errors reg. forgotten recalcs, and we to the complaints and reports about it,

and once it’s the tool for normal work there is no tool to check if it failed,

it’s not a tool, it’s a crutch, don’t get used to it!

This behaviour appears to have been fixed in LibO V4.2.3.3 (released April 2014). It was present in LibO V4.2.1.1.

I can’t see which source code change dealt with it.