[Issue 127479] New: MATCH function error when sheets are re-ordered and indirect addressing is used

[bu...@apache.org]

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: issues@openoffice.apache.org
          Reporter: RonBlackwell@sympatico.ca
  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.

[Issue 127479] MATCH function error when sheets are re-ordered and indirect addressing is used

[bu...@apache.org]

https://bz.apache.org/ooo/show_bug.cgi?id=127479

mroe <mr...@gmx.net> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|Windows 10                  |All

--- Comment #1 from mroe <mr...@gmx.net> ---
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.

-- 
You are receiving this mail because:
You are the assignee for the issue.