Forum Discussion
Excel crashing when combining BYCOL and INDIRECT
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.
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.
- djclementsMay 21, 2024Silver Contributor
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 ).
- Aaron_SolomonMay 22, 2024Copper Contributor
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.
- Aaron_SolomonMay 22, 2024Copper Contributor
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/ab3e2c65-a8c1-408d-bcc9-c9d523144eab?messageId=cbe94670-4c08-4f54-aa95-4daa1ea35a7b