The OFFSET function cannot use the return value of the INDIRECT function.

[Awilen-Bernkastel]

This is:

• 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?

Using the INDIRECT function as the first parameter of the OFFSET function should allow the OFFSET function to use the range pointed by INDIRECT.

What is the current behavior?

PhpSpreadsheet returns a rows/columns array from INDIRECT, and OFFSET cannot use it, expecting a string representing the cell addresses.

What are the steps to reproduce?

$filePath='Indirect named area for offset.xlsx';

$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($filePath);

$cellName = 'RESULTCELL';

$namedRange = $spreadsheet->getNamedRange($cellName);
if (!is_null($namedRange)) {
    var_dump($namedRange->getWorksheet()->getCell($namedRange->getCellsInRange()[0])->getCalculatedValue());
}

Observe the 0 result.

Here is an Excel file to reproduce this behavior with:

Indirect named area for offset.xlsx

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?

Not to my knowledge. The affected file format is Xlsx.

Which versions of PhpSpreadSheet and PHP are affected?

PhpSpreadSheet: 4.1.0
PHP: 8.1

[oleibman]

Confirmed. This could be very difficult to fix.

[oleibman]

This may be related to issue #4530.

[Awilen-Bernkastel]

If OFFSET were to take a PHP array as an argument, then I'd agree #4530 could have an effect on OFFSET due to a missing cell in memory leading to the issue with INDIRECT you linked to.

As far as I know, this is not the case as of PhpSpreadsheet 4.5.0, OFFSET instead taking string $cellAddress as its first argument.

[oleibman]

I think you're right. I am close to a solution for 4530, but it has no effect on this issue.