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.