As far as I can tell, Flexcel is transforming formulas that create dynamic ranges into array formulas. This does not produce the same results as Excel because the size of the array is not dynamic in the array formula. I handled a couple of cases before by saving my report template with mock data that produces the maximum size of the array but Its not possible to do in all cases. What is the proper procedure to handle this?
I only need the formula to remain exactly the same as is was in the template, I'm not calculating anything with flexcel, just filling data and setting named ranges.
Hi,
Sorry, but dynamic arrays aren't supported yet. FlexCel isn't really converting the formulas to array formulas, it is Excel that converts the array formulas to dynamic arrays if the tool that wrote the file identifies as an Excel version that supports dynamic arrays.
Excel up to and including Excel 2021 doesn't support dynamic arrays, so that's why the formulas are written as standard array formulas. So older Excel versions (And tools like FlexCel or libreoffice) can show the file. You can verify it yourself by opening the file in LibreOffice, and looking as how all the dynamic arrays are "converted" to array formulas. As said, they aren't really converted, they are saved like that in the file.
We are working in supporting dynamic arrays, but they basically break everything, even a lot of existing files (and Excel breaks them too, by the way), so I can't tell you an estimate on when they could be ready.
Thanks for the fast response. I will try to work around this. Can I use a combination of TExcelfileFormat / TExcelVersion and other settings to avoid this? I understand that this could bring other compatibility problems, but in this case its just for a particular file that only uses a FILTER function over a huge table.
Sadly we need to write some metadata in the cell itself that tells excel this is not an array formula (because it could be too). So TExcelVersion is not going to help. FlexCel is currently not awayre of this cell metadata, so it doesn't write it, and that is why they are all dynamic arrays (besides the metadata telling that the file was written by a tool that understood them)