Calculate for OFFSET doesn't work properly

[jsfkcz]

Hello,
I have two formulas with OFFSET function in my "template" (it equals czech function POSUN).
Both give the same result. Default value of offset is 1 and results are "A".

XLSRW writes value 2 to the cell B2 and uses calculate at the end.
If I open the result, everything seems to be ok - the results are "B" .

But if I change value in B2 cell manually - nothing happen.
The result values "B" are "frozen".

After Ctr+Alt+F9 everything become OK and formulas giving the corect values by the number in "B2" cell.

Note: This frozen state is under Excel 2007 and 2010 (under Excel 2013 it works ok ).
Tested at 5.20.46 and 5.20.49 with the same behavior.

The template and result are at http://www.genet.cz/kohout/xlsrw/xlsrw_offset.zip for download.

Josef Kohout

[larsa]

Hello

I couldn't reproduce this. Tested your file with excel 2007 and excel 2013.

[jsfkcz]

Hello,
I had to test this function at the our customers' sides.
At the first customer there were Excel 2007 without any actualization. And this version have still problem with offset.

These days I tested to open "problematic" result under Excel 2007 with all servicepacks .

Under this version, everything seems to be ok.

At the second customer I had to ask another person to test this problem under Excel 2010. Unfortunatelly there weren't the same conditions I used at the first one.

So I made a new test environment with Excel 2010 too and tested this problem again.

Now I can say, that there is no calculation problem with offset function at XLSRW.

I am sorry for misinformation.

Josef Kohout

[jsfkcz]

Hello,

I have still problem with "frozen" values .
So I had to make another tests ....... .

Templates which I made under Excel 2007 SP0 (SP0=without servicepack) work different than templates which I made under Excel 2007 with SP3 !!!

If I make the result by template created under "SP0", everything is ok under Excel 2007 SP3, Excel 2010, Excel 2013 .

If I make the result by template created under "SP3", formulas with OFFSET and INDIRECT functions are frozen if I open the result under Excel 2007 SP3 (opening under Excel 2010 and 2013 seems to be ok).

When I was making the SP3 template, I opened the SP0 template and saved it as "SP3" name without any changes under Excel 2007 SP3.

For the first look, everything is correct in the SP3 result file, but if I change value in B2 cell manually, nothing will happen.
First values in result were correct by the XLSRW "calculate", but next hand made changes in result in the cell "B2" do nothing till Ctrl+Alt+F9 .

After Ctrl+Alt+F9, every next manually changes at the cell B2 will make correct changes at cells with formulas.

So I compared every parts of xlsx templates SP0 and SP3 with this result:

\docProps\core.xml - contain another timestamp (it is ok)
\xl\calcChain.xml - there is change in order
sp0: <c r="B12"/><c r="B11"/>
sp3: <c r="B11"/><c r="B12"/>
\xl\workbook.xml - there is change in build
sp0: rupBuild="4505" calcId="124519"
sp3: rupBuild="4506" calcId="125725"

These changes are enought to get "frozen" cells.
I do not know why..... .

And there is another importatnt thing!
There aren't calcChain.xml In the both result files.
( It is not important for the "SP0" result, but for the "SP3" result probably yes.)

If I manually insert the file calcChain.xml from file XLSRW_template_indirect_SP3.xlsx to the "frozen" result file XLSRW_result_indirect_SP3.xlsx and open the result file XLSRW_result_indirect_SP3.xlsx, everything is ok.


Every template and result files are for download at http://www.genet.cz/kohout/xlsrw/xlsrw_ ... p0_sp3.zip

Any idea ?


Josef Kohout

(I made a whole new template under Excel 2007 SP3 too, but the result had the same frozen values under Excel 2007 SP3)

[larsa]

Hello

The problem is the calcId attiribute, calcPr element in workbook.xml substream. It shall have the value 124519. In xxx_SP3 it is125725. I think this is some kind of excel bug, as the files works correct in Excel 2013. Will fix this in the next update.

[jsfkcz]

Hello,
I made next templates created one by one under 2007SP3, 2010SP2 and 2013 and analyzed their rupBuild and calcId values and their behavior in result files. Templates work correct under each version of Excel.
The results were created by XLSRW 5.20.50 .

The results are:

template created under 2007SP3 rupBuild=4506 and calcId=125725
result opened under 2007SP3 frozen till Ctrl+Alt+F9
result opened under 2010SP2 ok
result opened under 2013 ok

template created under 2010SP2 rupBuild=9303 and calcId=145621
result opened under 2007SP3 frozen till Ctrl+Alt+F9
result opened under 2010SP2 frozen till Ctrl+Alt+F9
result opened under 2013 ok

template created under 2013 rupBuild=14420 and calcId=152511
result opened under 2007SP3 frozen till Ctrl+Alt+F9
result opened under 2010SP2 frozen till Ctrl+Alt+F9
result opened under 2013 frozen till Ctrl+Alt+F9

Every templates and result files are for download at http://www.genet.cz/kohout/xlsrw/xlsrw_ ... rsions.zip



Josef Kohout

[larsa]

Hello

CalId shall be zero. Tested with Excel 2007 and 2013. All your templates calculate correct.