https://bz.apache.org/ooo/show_bug.cgi?id=127479
Issue ID: 127479
Issue Type: DEFECT
Summary: MATCH function error when sheets are re-ordered and
indirect addressing is used
Product: Calc
Version: 4.1.3
Hardware: PC
OS: Windows 10
Status: UNCONFIRMED
Severity: Normal
Priority: P5 (lowest)
Component: programming
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: ---
Created attachment 86180
--> https://bz.apache.org/ooo/attachment.cgi?id=86180&action=edit
MATCH function error when sheets are re-ordered
MATCH returns the wrong offset when used with INDIRECT and ADDRESS to specify
the look-up array and the order of sheets is changed. In the attached
spreadsheet, sheets are to be searched in the order they appear - I.E.
initially A1:A10 on sheet "ROW5" should be searched, then, if sheet "ROW7" is
moved between "MATCH" and "ROW5", A1:A10 on "ROW7" should be searched first.
The function
MATCH(SEARCH;INDIRECT(ADDRESS(1;1;4;1;SHEETNAME)&ADDRESS(10;1;4;1));0) does not
work correctly: even though the cell containing the "SHEETNAME" is set to
"ROW7" after the move, MATCH returns the offset to the SEARCH value on the
"ROW5" sheet.
There is a work-around which is to copy all the look-up arrays onto one sheet
using INDIRECT(ADDRESS(row,column;4;1;sheet) functions and then use the MATCH
function to search the copies.
P.S. Don't use UNDO to reset the sheet order: CALC really messes thing up if
you do.
--
You are receiving this mail because:
You are the assignee for the issue.
