I have a big sheet with a lot of formulas that have a dependency hierarchy between them. It starts with a cell with a date value. Then, cell x:y (and others), has formula depending on this date. Then cell w:z (and others) has a formula depending on cell x:y. And so on...
This main cell with a date value is filled using apache poi.
And now my problem: when I open the generated excel file, the date is there, but none of the formulas are calculated. They all have the error "A value used in the formula is of the wrong data type". It seems that when the formula try to evaluate it self the date isn't there yet.
Solutions: 1) If I click in the cell, and just press ENTER, the formula is correctly evaluated. But then I would have to do this for all cells. 2) I click in the date cell, copy it, and then paste it in the same place, and all formulas in the sheet are evaluated! 3) I could iterate in all cells in my application, evaluating each one with evaluateFormulaCell method from FormulaEvaluator class. But I have a lot of formulas and the performance of this is terrible.
Does someone have a solution for this?
Thanks!!
To solve this, I changed all formulas in my template, to use instead of the regular formula sintax (=SUM(A:D)) the following sintax: $[SUM(A:D)]