We encountered an issue when working with large array formulas in our excelsheet. The array formula is supposed to take data updates from the next sheet, but since the last spreadsheets update, the array formula does not update the values anymore. Only when copy/pasting the formulas again, it shows an updated version.
This issue is reported by various other users in our organisation, across different OS and devices. The issue has been reported at the same time as the dropdown issues since last OO update.
Video is included for explanations.
Hope this can be solved soon, the sheets are essential in our daily work
Yes this works! Which is already helpful. However, these calculations used to happen automatically and not require any further actions. Do you have a solution for that in mind?
You can simply set a macros to autostart that will recalculate all workbook/active sheet on any change. However, keep in mind that it may cause some lag depending on the complexity of the file.
Macro for the reference:
Api.attachEvent("onWorksheetChange", function() {
Asc.c_oAscCalculateType.All; // If want to calculate whole workbook
Asc.c_oAscCalculateType.ActiveSheet; // If want to calculate only active sheet
console.log("Calculated");
});
Set it to Autostart by inserting the macro, hitting three dots in the list and selecting Make autostart. You will see that on the left of the macro name (A) appears, which means that it is set to autostart.
Alternatively, you can just run it manually when you need to have automatic recalculation.
I think automatic calculation was disabled due to heavy load when calculating complex files.
Hey there, Constantine, thanks a lot for the explanation.
Your explanation is a bit too technical though, I don’t know what a macro is for example. I found the menu, which part of the above macro part do I copy and paste it where exactly? Thanks a lot if you can explain
Macros is generally a system plugin that allows running various Office API methods in the editor. It is available in View tab on the main toolbar or in Plugins tab for versions of Document Server lower then 8.2.
When opening this plugin, you will see a window where you can insert your code. In case of mentioned macro, you should insert whole sample.
Still struggling a little bit.
I managed by making it work in one workbook. I had the change the macro from “onWorksheetChange” to “onWorkbookChange” because the array formula relates to the whole workbook.
Now the problem is, in one workbook it’s fully functional. But on another one where I try to apply the same, nothing happens. Any suggestions? Here’s the screenshot.
