Formulas won't calculate automatically

Question

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!!

K_B · Answer 1 · 2013-03-13T15:32:26.007

1

Your date value isnt recognized by the excel formulas as a date, what you could do is have another cell dat will first take the datevalue DATEVALUE() of the cell filled using Apache poi.

Then direct your first level formulas to that cell instead of the one filled using Apache poi

edited Mar 13 '13 at 15:32

answered Mar 13 '13 at 15:14

K_B

3,668
1
19
29

Thanks for the suggestion. I tried here, but the same error was shown in the new cell that uses the DATEVALUE() function referring to the poi filled cell. For now I'm using the evaluateFormulaCell approach, but I don't know if it is the best solution. – qxlab Mar 13 '13 at 16:00
does `=TEXT()` work or just `=()`? If not then apparently the Apache result isnt really there (whatever that may mean) – K_B Mar 13 '13 at 16:04
I used =(), and when I opened the file, in this cell I could see the POI formula (`{obj.date}`), but when I clicked in this same cell I could see the formula `=(K6)` and if I press enter the formula is correctly evaluate as the rest of the formulas in the sheet. – qxlab Mar 13 '13 at 16:26
1

alternatively, if you know how to create a VBA code, you could build a little VBA script that runs on Worksheet_Open that will repeat your Solution 2) (just record the macro and put it in the Worksheet_Open script) – K_B Mar 13 '13 at 16:37

score 1 · Answer 2 · edited Dec 09 '14 at 01:35

1

Because Excel don't calculale automatically, so just change this.

Excel 2010: File -> Options -> Formulas: Find Workbook calculation, and change to Automatically.

It worked for me.

edited Dec 09 '14 at 01:35

Community

1
1

answered Sep 13 '13 at 11:59

drakid

320
4
13

score 0 · Accepted Answer · answered Mar 26 '13 at 20:59

0

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)]

http://jxls.sourceforge.net/reference/formulas.html

answered Mar 26 '13 at 20:59

qxlab

1,506
4
20
48

Formulas won't calculate automatically

3 Answers3