[Python] How to automatically trigger Ctrl+Shift+Enter for array formulas in Excel after...

Hi everyone,

I have a main Excel workbook containing array formulas, which reference data from several sub-workbooks (or sheets). The workflow is as follows:

The main workbook initially does not contain the sub-sheets. Using Python (openpyxl), I dynamically add the sub-sheets and fill them with data. The main workbook is then copied into a report workbook. Here’s the problem: My array formula in the main workbook: {=VALUE('0001'!F94:F117)} does not automatically recalculate after the sub-sheets are added. The formula shows #REF! initially. The only way to make it recalc correctly is to click into the cell, do nothing, and press Ctrl+Shift+Enter manually. I have tried: Reading the cell value and writing it back (copy-paste the same value). Changing calculation mode. Using openpyxl’s ArrayFormula functionality. Nothing works — the formula only updates after manually pressing Ctrl+Shift+Enter. I want to automate this process, so that after adding sub-sheets with Python, the array formulas in the main workbook update automatically without manual intervention.

Has anyone successfully solved this? How can I trigger the equivalent of Ctrl+Shift+Enter programmatically in Python or otherwise automate this recalculation for array formulas?

Thank you in advance!

I have tried using openpyxl and xlwings, but nothing worked

Continue reading...