Cannot set validation for a cell / Validation setting being removed if a formula

[sgiacinto]

Hello, If I setup a file in Excel with simple cell validation against a named range, this works when the file is updated and saved in php_excel.

e.g. when source = "=Ethnicity" and there is a named range called Ethnicity. This range is added by php_excel dynamically.

However if I have a formula as the list's validation source, the validation configuration is deleted when saved.

=INDIRECT(VLOOKUP($C$3;LOOKUPS!$L$1:$M$9;2))

which is looking at the value in $C$3 (the result of "Ethnicity") and looking it up in the range L$1:M$9 and using the name there as the named range to list.

The formula =INDIRECT(... above is completely removed and there is no data validation on the cell after saving it in php_excel.

Anyone have any ideas?

[sgiacinto]

Sorry, forgot to add, this works in Excel.

[johmue]

Is it possible for you to post a very basic example that outlines the core problem - maybe 5-10 lines of php code? Thx

[sgiacinto]

I have reduced it down to something understandable. The template file worksheet contains these validation nodes:

<dataValidations count="2">
<dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="C3">
<formula1>Groups</formula1>
</dataValidation>
<dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="C4">
<formula1>INDIRECT(C3,0)</formula1>
</dataValidation>
</dataValidations>

The first node which is a direct reference to a dynamically created named range, functions fine. The second node, which is a dynamic reference to the value from the first node, is completely removed when opened in php_excel.

[johmue]

If this can be tracked down to Excels XML it's an issue with the LibXL library. Since the PHP extensions is just a wrapper around the library you should probably point libxls support to this issue here so that they can investigate.

[johmue]

Can you please check with latest libXL 3.8.0?