INDIRECT and Relative Address When Used as Part of Range

[oleibman]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

See "current behavior" section below.

What is the current behavior?

PR #3673 solved some problems with relative addressing in Defined Names. As part of the investigation surrounding that PR, I identified some cases which are still not handled correctly; I proceeded with the PR knowing of these problems because it was an improvement on what was available before and I was unfortunately not able to gain any traction on the problems. This issue deals with the use of the INDIRECT function. The following code is common to all of the examples below.

        $spreadsheet = new Spreadsheet();
        $spreadsheet->addNamedFormula(
            new NamedFormula('SumAbove', $spreadsheet->getActiveSheet(), '=SUM(INDIRECT(ADDRESS(1,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))')
        );
        $sheet = $spreadsheet->getActiveSheet();

First of all, here's something that works:

        $sheet->getCell('A1')->setValue(100);
        $sheet->getCell('A2')->setValue(200);
        $sheet->getCell('A3')->setValue(300);
        $sheet->getCell('A4')->setValue(400);
        $sheet->getCell('A5')->setValue(500);
        $sheet->getCell('A6')->setValue('=SUM(A$1:INDIRECT(ADDRESS(ROW()-1,COLUMN())))'); // 1500

The calculated value in A6 is 1500, as it should be.

Here's some similar code that does not work.

        $sheet->getCell('B1')->setValue(10);
        $sheet->getCell('B2')->setValue(20);
        $sheet->getCell('B3')->setValue(30);
        $sheet->getCell('B4')->setValue(40);
        $sheet->getCell('B5')->setValue('=SumAbove'); // 100

The calculated value in A6 is 40, not 100. It seems to use only the cell above when evaluating SumAbove, not the entire column.

Here's some code that fails differently (reversing columns A and B from the prior examples).

        $sheet->getCell('B1')->setValue(100);
        $sheet->getCell('B2')->setValue(200);
        $sheet->getCell('B3')->setValue(300);
        $sheet->getCell('B4')->setValue(400);
        $sheet->getCell('B5')->setValue(500);
        $sheet->getCell('B6')->setValue('=SUM(A$1:INDIRECT(ADDRESS(ROW()-1,COLUMN())))'); // 1500

        $sheet->getCell('A1')->setValue(10);
        $sheet->getCell('A2')->setValue(20);
        $sheet->getCell('A3')->setValue(30);
        $sheet->getCell('A4')->setValue(40);
        $sheet->getCell('A5')->setValue('=SumAbove'); // 100

The calculated Value in B6 is 1640, not the expected 1500. The errant result appears to be SUM(A1:B5) (rather than B1:B5), with A5 incorrectly evaluated as 40 (see above).

Finally, here's a problem that seems related, but with an entirely different symptom - it seems to put the calculation engine in a loop. See ReferenceHelper3Test.

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->addNamedRange(new NamedRange('AboveCell', $sheet, 'A1048576'));
        $sheet->setCellValue('C2', 123);
        $sheet->setCellValue('C3', '=AboveCell');
        $sheet->fromArray([
            ['Column 1', 'Column 2'],
            [2, 1],
            [4, 3],
            [6, 5],
            [8, 7],
            [10, 9],
            [12, 11],
            [14, 13],
            [16, 15],
            ['=SUM(A2:AboveCell)', '=SUM(B2:AboveCell)'],
        ], null, 'A1', true);
        $sheet->getCell('A10')->getCalculatedValue();

Note that there is no problem getting the calculated value for C3.

What are the steps to reproduce?

See "current behavior" section above.

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

It definitely affects Xlsx. I would be surprised if all other formats were not similarly affected.

Which versions of PhpSpreadsheet and PHP are affected?

All.