ANCHORARRAY() doen't work in validation

[j4vs]

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?

The correct rendered of ANCHORARRAY() in validatios

What is the current behavior?

When use ANCHORARRAY() in validations, doesn't rendered

$validation->setFormula1('=ANCHORARRAY($B$1)');

What are the steps to reproduce?

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:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$validation = $sheet->getCell("A1")->getDataValidation();

$validation->setType(DataValidation::TYPE_LIST);
$validation->setErrorStyle(DataValidation::STYLE_STOP);
$validation->setAllowBlank(true);
$validation->setShowDropDown(true);
$validation->setFormula1('=ANCHORARRAY($B$1)');

$sheet->getCell("A1")->setDataValidation($validation);

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

What features do you think are causing the issue

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

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

Which versions of PhpSpreadsheet and PHP are affected?

[oleibman]

Confirmed. PhpSpreadsheet is not adding the prefix which Excel requires for functions introduced after Excel 2007 (like ANCHORARRAY) to formulas used in Data Validation. While I work out what needs to be done to PhpSpreadsheet, you may be able to work around the problem using the following statement:

$validation->setFormula1('_xlfn.ANCHORARRAY($B$1)');

That should be good enough for write. I am unsure whether it will be read correctly when you load a spreadsheet.

[oleibman]

It looks relatively straightforward to handle the formula on read and write. What is not straightforward is getting the DataValidator::isValid method working with a list specified in this manner. How important is that to you?