Excel Crashes when sorting a table with CALCULATED dates

Question

I have a workbook which was working perfectly until a few days ago!

I have a table which has several calculated date fields, using an array formula to find the latest date in another table.  The formula works perfectly and returns the correct results.

However, I can no longer sort the table which contains these calculated dates.  Even if I try to sort by other fields (e.g. name), Excel just crashes.  

Sorting other tables which contain manually entered dates is not an issue.  If I delete all the array formulas, the sort works.  

I can only assume that a recent update has caused this problem as it was previously working.

UPDATE:  I just tried this in Excel 2010 and it works perfectly!  The problem definitely seems to be related to a recent Excel 2016 update!

Answer 1

We suggest you to post your query to MSDN forumsto check the formula you're using and know the reason why it's crashing since this is related to VBA concern.

Do let us know if you have any other concerns.

Answer 2

Thanks for your response.

I have done all of the things you suggested above (although the Repair just uninstalled and reinstalled Office!), but I followed the instructions and did a manual uninstall and reinstall.

However, the problem persists!

It looks like I can no longer use array formulas in tables!  The sort works once, then it's almost as if the sort corrupts the array formula and, if I try to sort again, Excel crashes.  If I delete the array formulas and re-enter them, I get one good sort, then exactly the same thing happens.

For now I will have to find a workaround but please report this back to the Excel team at Microsoft.  I use tables and array formulas in lots of my solutions and I really don't want to have to rewrite everything (and in some cases this will not be feasible without VBA and I don't really want to have to write code where a formula has and should work).

Thanks

Shirley

Answer 3

Hi Shirley,

Excel app may crash or freeze unexpectedly.

To help you resolve your concern, kindly follow the troubleshooting steps on the Repair an Office application. We also suggest that you install the latest update. You may refer to this link.

If the issue persist, kindly uninstall and reinstall the application. Right-click the Start button  (lower-left corner), and choose Control Panel. elect Programs and Features, then right-click your Microsoft Office product, and choose **Uninstall.**To reinstall Office, see Download and install or reinstall Office on your PC or Mac and follow the steps for your Office plan.

Do let us know if you need further assistance.