The OFFSET function doesn't take defined named ranges into account.

[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 OFFSET function on a named range should lookup elements in the named range.

What is the current behavior?

PhpSpreadsheet doesn't transform named ranges into valid cell coordinates in the OFFSET function.

What are the steps to reproduce?

$filePath='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 empty result.

Here is an Excel file to reproduce this behavior with:

Named area for offset.xlsx

The file contains a named area, and a call to the OFFSET function on that area. Cell D1 (RESULTCELL) should contain the content of the only filled cell in the named area.

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.0.0
PHP: 8.1

[oleibman]

I already had to make changes to OFFSET in PR 4360 for your apostrophe problem. The change that I just pushed to that PR should fix this problem too.

[Awilen-Bernkastel]

Thank you very much. I have tried this last commit, to positive results even for the long chain of formulae I'm dealing with in the spreadsheet this issue arose in.

I'm closing this issue, and I wish you a wonderful day.