Forum Discussion
Excel crashing when combining BYCOL and INDIRECT
Following recent updates (Excel latest version and/or MacOS 14.5 ?), Excel crashes systematically on Mac OS 14.5, with a simple formula using BYCOL & INDIRECT:
=BYCOL(A3:E3;LAMBDA(col;INDIRECT(INDEX(col;1))))
with following data starting in A1
| toto1 | toto2 | toto3 | toto4 | toto5 |
| A1 | A2 | A3 | A4 | A5 |
my configuration : MacOS 14.5 on Macbook pro 16 with Apple M3 Max + Excel version 16.85 (24051214) with Microsoft 365 subscription
a few days ago (before latest updates) it wouldn't crash.
Thanks for your support.
5 Replies
Aaron_Solomon In which cell was the formula entered? If it was entered in cell A2, A4 or A5, it will produce a circular reference warning (based on the sample data you've shared). Are you sure the values in range A3:E3 are correct? Should they not be A1, B1, C1, D1 and E1?
Having said that, the formula does not cause my version of Excel to crash, regardless of the values entered in range A3:E3. Alternatively, you could try using the MAP function in place of BYCOL:
=MAP(A3:E3;LAMBDA(val;INDIRECT(val)))If this is still causing Excel to crash, you may want to try repairing your Office installation.
- djclements, thank you for your reply, you are correct the values in range A3:E3 should be A1 - E1. The formula still lets excel crash. And if I remove the INDEX(col;1) and replace it by col I get a message from Excel "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated."
I also tried your alternative with MAP (as well as with SCAN), both have the same results : crash with INDEX, and lack of resources without INDEX.Aaron_Solomon Since you're getting the "Excel ran out of resources" error message, a good place to start would be to check the current memory usage on your system using the Activity Monitor (see: https://support.apple.com/en-ca/guide/activity-monitor/actmntr1004/mac ).
You could try to free up memory by closing any apps running in the background that you currently don't need open (see: https://support.apple.com/en-ca/guide/activity-monitor/actmntr1002/mac ).
