Issue 127479 - MATCH function error when sheets are re-ordered and indirect addressing is used
Summary: MATCH function error when sheets are re-ordered and indirect addressing is used
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: 4.1.3
Hardware: PC All
: P5 (lowest) Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2017-07-17 01:08 UTC by RonBlackwell
Modified: 2017-07-17 07:18 UTC (History)
0 users

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

MATCH function error when sheets are re-ordered (15.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-07-17 01:08 UTC, RonBlackwell
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description RonBlackwell 2017-07-17 01:08:32 UTC
Created attachment 86180 [details]
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.
Comment 1 mroe 2017-07-17 07:18:36 UTC
The MATCH function works correct but it seems that there is a missing trigger for the INDIRECT function to recalculate the new string. (Maybe a bug or maybe there is a reason for it.)

Pressing [Ctrl]+[Shift]+[F9] (hard recalculation of all) gives the right result.