Do you want to: Report a bug
OS version: Windows 11 Home 25H2
App version: Community-Version 9.2.0.100 (x64 exe)
Downloaded from: ONLYOFFICE website
I have a spreadsheet with 2 work sheets. If I open this file, data in the first sheet is not fetched from the second sheet, because the formulas in a table on the second sheet are not calculated automatically. Only if I press F9 the results are calculated correctly.
As I do not find a setting, I assume that this is a bug.
Regards,
sipa66
Hi @sipa66
You might need to be more specific about the nature of your issue, as in provide an example of the issue.
I just did a quick test;
On one sheet, I had a cell contain a sum which included a random number.
And then on another sheet, I had another random number added to the result of the previous sheet.
The random number from both sheets was visible on the first sheet.
Every time I opened the spreadsheet from the Recent files list, or opened the sheet from the File explorer, the dynamic values from both sheets had recalculated, yielding a new random number, resulting in a different value each time.
All sheets would appear to be recalculating on open/launch, and I am on the same version as you indicate.
/David…
CalcBug.xlsx (9.6 KB)
Please see the attached example file which shows the bug. Cell Data!G2 is only calculated if I press F9.
I was initially stumped by the numbers; in my region, a decimal place is denoted by period ( . ), but you use a comma ( , ), so that didn’t work at all until I changed it. But that aside.
Now, the issue lies in how and what triggers recalculation.
I suspect for the most part, changing a cell’s value will trigger a recalculation of the whole workbook. The value of each calculated cell is stored with the formula, and this is saved in the file when closed.
The sample you provided is loaded with static data, and as values are stored with formulas, no recalculation is performed upon opening.
I confirmed this by editing the spreadsheet with a text editor and changing the value of one of the cells, and then opened the workbook normally to see that value of the cell I had changed was in effect, but the sheet with the reference to the other sheet still held the original value until I forced a recalculation.
What is different to your case from my test is my cells generating a random number. And because the cell values are dynamic, there is a special parameter attached to the formula cell which seems to force recalculation upon opening because they are referenced.
I tried this on your sample, and it worked.
This is however, not something that you could implement in your case in a practical sense.
I did try to add a single cell into one of the sheets which had an auto-recalculation attribute, but this only effected that cell alone, and as it was not referenced by other cells in the sheet, it had no knock-on effect.
The Only Office support team may have an option for you, but my impression is that you will need to manually refresh if any of the “static” data changes while the workbook is closed.
It gets really technical from here on…
If you really want the “nuts & bolts” about what I did; well, the sheet stores all the sheet as an XML file, and your cell content as
<c r="B2" s="2"><f ca="1">IFERROR(VALUE(MID(Billing!B2,FIND("-",Billing!B2)+1,99)),0)</f><v>5.5499999999999998</v></c>
• The c tag is a cell
• The r attribute is the cell co-ordinate, column B row 2
• The f tag holds the formula for the cell. I added the ca attribute to force recalculation
• The v tag holds the last known result of the formula
If you are not familiar on how to see this side of a spreadsheet, then it is probably better not to tamper, for at this level, a mistake could make a real mess.
Ah, okay. Thx for your detailed explanation. I have added Zufallszahl() to the formula where the text length is defined. Now it forces recalculation as a workaround.
=WENNFEHLER(WERT(TEIL('Billing'!B2;FINDEN("-";'Billing'!B2)+1;99+ZUFALLSZAHL()));0)
That’s good!
I didn’t think about using a random number as one of the range parameters of TEIL/MID(). I wish that idea had come to me. It was surprising that a parameter can be a Real number without causing an error.
Well, at least your issue is resolved.