Forum Discussion
Error "you can't change part of an array" when inserting a row in a protected sheet (Version 2401)
I have no Excel 2019 to test, but yes, that could be. Build number means nothing if you compare different Excels. It means nothing as well if to compare functionality on different channels in subscription model (365).
In your question you compare Office 2019 and Excel 365 on semi-annual channel. Both have the latest on today versions
2019:
365 semi-annual:
but most probably above 365 has more functionality compare to 2019.
As for the concrete issue - 2019 has only security updates till end of the support. It works as it works, I'd don't expect any changes in behaviour for the 2019.
The image you sent confirms that "Insert rows" is allowed in the sheet protection settings, further supporting the notion of a bug in Excel version 2401 (built 17231.20236) that causes the "you can't change part of an array" error when inserting rows in a protected sheet containing array formulas.
Here's a summary of the issue and potential solutions:
Understanding the Issue:
- Version-Specific Bug: This issue is likely due to a bug introduced in Excel 2401 related to how it handles protected range formulas with arrays and row insertion.
- Conflicting Changes: There might be a conflict between how Excel 2401 modifies protected array formulas and the process of inserting rows.
Workarounds:
While there's no official fix from Microsoft yet, here are some workarounds you can try:
- Upgrade Excel: If possible, consider upgrading to a newer version of Excel beyond 2401, as Microsoft might have addressed the bug in subsequent updates.
- Modify Formula Structure:
- Avoid using arrays in protected ranges.
- Use helper columns or alternative formulas that don't rely on arrays.
- Consider using SUMPRODUCT or SUMIF instead of array formulas.
- Insert Rows Using VBA:
- Create a VBA macro to insert rows programmatically, potentially bypassing the manual insertion error.
- Unprotect Sheet (Temporary):
- Unprotect the sheet before inserting rows, then reapply protection.
Additional Considerations:
- Double-check sheet protection settings: Ensure "Insert rows" is allowed, as confirmed by the image.
- Compatibility mode: If using an older workbook, consider saving it in a newer format compatible with Excel 2401.
Microsoft Feedback Portal:
- The issue is reported on the Microsoft Feedback Portal: https://feedbackportal.microsoft.com/feedback/idea/e7b37f80-2acf-ee11-92bd-000d3a7a5824
- Upvote and comment to raise awareness and encourage a fix.
Awaiting a Fix:
- A permanent solution likely requires a fix from Microsoft in a future update.
By following these recommendations and engaging with Microsoft's feedback process, you can mitigate the issue while awaiting an official resolution
- Thanks you for your advices.
Arrays formulas are used to automatically repeat formula when inserting a new row.
Up to now, it has been decided to not use VBA in order to avoid to manage Trusted Certificate signature.
