Apache OpenOffice (AOO) Bugzilla – Issue 125867
formulae with inverted cell range not corrected/resolved until file is saved and reopened
Last modified: 2015-01-08 00:20:24 UTC
formulae with inverted cell range not corrected/resolved until file is saved and reopened; - create a new spreadsheet - create column A containing random text some of which is repeated, eg cells A1:A10; sdcsdv, dfv, dfv, fdv, dfv, dfv, dvffd, dfv, dvffd, dvffd - add this formulae to B2: =IF(MATCH(A2;A1:A$1;0);1;0) [issue doesn't occur with =IF(MATCH(A2;A$1:A1;0);1;0)] - copy and paste (or drag) formulae into remaining B cells - note that the values in the formula have been correctly incremented for each cell (and the red and blue coloured boxes are highlighting these correctly) - note the formula is not however being resolved correctly in any cell (in column B) - save calc file (eg file.ods) and reload file - note the formulae cell range definitions have have been corrected (inverted) in all B cells except B2; eg =IF(MATCH(A3;A2:A$1;0);1;0) has become =IF(MATCH(A3;A1:A$2;0);1;0) - note the formula is now being resolved correctly in all cells (in column B) I recall encountering this issue in the past without the MATCH function, so it is probably a more general issue. Possibly calc functions do not accept inverted cell range input? If this is the case then shouldn't calc throw an error? Note the issue does not occur the same way when the formula is created in B3; - create a new spreadsheet - create column A containing random text some of which is repeated, eg cells A1:A10; sdcsdv, dfv, dfv, fdv, dfv, dfv, dvffd, dfv, dvffd, dvffd - add this formulae to B3: =IF(MATCH(A3;A1:A$2;0);1;0) - copy and paste (or drag) formulae into remaining B cells - note the formula is not being resolved correctly in any cell (in column B) - save calc file (eg file.ods) and reload file - note that no changes to the formulae have been made by calc
Your bug report is being closed as NOT_AN_ISSUE. We encourage you to retest your bug against the latest release. If the issue is still present in the latest stable release, we need a sample spreadsheet to reproduce.
I have confirmed the issue occurs on both Linux and Windows, Apache OpenOffice 4.0.1, 4.1.0, and 4.1.1. Ensure A1 is sdcsdv, A2 is dfv, A3 is dfv etc. An example spreadsheet has not been provided because once the spreadsheet is saved/loaded (as .ODS) all cells except B2 are automatically corrected as per above; meaning there is no way of observing the issue.
Created attachment 84276 [details] Screenshot of your testcase Your formula =IF(MATCH(A2;A1:A$1;0);1;0) in B2 result #N/A
I can reproduce the problem in OO 4.1.1 on Linux(i686). It seems that (in the specific case of A1:A$1) the drag-fill operation skips the normalization of the ranges (high:low => low:high) that otherwise occurs. For some functions this is not a problem; COUNTA works fine with A10:A$1 but MATCH does not.