Excel crashing when combining BYCOL and INDIRECT

Copper Contributor

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

toto1toto2toto3toto4toto5
     
A1A2A3A4A5

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 ).

@djclements, I checked the memory usage in the Activity Monitor, before loading the file, after loading the file, after un-commenting the formula so that Excel executes it : Excel memory usage never exceeds 1GByte. My Mac has 64Gbytes Ram and it seems plenty is available (>> 1 GByte), here is a screen capture of my Activity Monitor for the memory usage.

Capture d’écran 2024-05-22 à 10.44.43.png

@djclements, I checked on internet, it seems other are having the same issue. Following the recommendation provided on below thread where it's stated that similar problems are know issue with Excel 16.85, I downgraded to Excel 16.84 and the problem no more occurs. Thanks for your support !
https://answers.microsoft.com/en-us/msoffice/forum/all/excel-crashes-on-certains-workbook-on-macos/a...