I have an excel file from a customer which contains 5 sheets. I need to enter data in the first 2 and then create a pdf from the 3rd, 4th or 5th sheet.
Data from sheet 1 and 2 is referenced on 3, 4 and 5.
This mostly works except for one cell, that is not updating.
I save both the pdf and a copy of the filled-out xlsx file.
If I open the resulting xlsx file a message is shown if I want to enable editing. As soon as I click that button, the cell referencing another cell on sheet 1 is updated correctly.
I tried to add Recalc commands but this does not help.
What can be the issue?
I am using Delphi and installed the latest version of the component before posting this.
It is hard to say without looking at the file, but from the top of my head, what I can think is:
There is some macro in the file updating the values of the cell. (FlexCel doesn't run macros)
This is a dynamic array formula (see for example https://exceljet.net/articles/dynamic-array-formulas-in-excel ) Sadly we don't support recalculating dynamic array formulas yet. We do support array formulas, but not dynamic array formulas, because they change the basics of how a spreadsheet works. You now can have empty cells with values, which basically breaks everything.
If it isn't macros or a dynamic array formula, can you share a small file that reproduces the error so we can take a look.
When looking closer to the formula, there was an @-sign before it. And it referenced a range (probably from selecting a merged cell).
I replaced it with a reference (without @) to the first cell of the merged cells and it works.
Thanks for the hint.