Can't save file if formula too complex?

This one is baffling me. Can someone test it out on their system? File is attached.

Problem:
File has 7000 rows of data. A somewhat complex lookup formula is used to pull data from another source based on a particular cell value. The formula works as intended, and is replicated for about 50 rows or so. File saves no problem.

When copying the formula to the last row, everything seems fine. Calculation works, file updates with correct data. But when trying to save, Excel crashes. Column next to it with different formula causes no issues - copy to row 7000, save no problem.

Bizarre. Happens whether file is .xlsx or .xslb. Have tried rebuilding file with no success. Even went as extreme as uninstalling MS Office and reinstalling. Same issue.

Office 365 on a MacBook Pro FWIW.

To isolate whether this was an issue with the lookup to another file, I even brought over all of the data from the other file into a new tab and saved it as values (read: no secondary lookups). Repointed the formula to the tab rather than the other file. Now everything is within one file.

Copied rows to end. Calculated correctly. Save ... crash.

???

To replicate:

Upon opening, don't update data.

Go to the last row of data in column I. Copy it down to the last row of the table (row 7766). Save. Note no issues. File will save.

Go to the last row of data in column H. Copy it down 2 or 3 rows. Save. Note no issues.

Go to new last row of data in column H. Copy it down to the last row of the table (roe 7766). Note that the data updates (you can tell by clicking the filter and noting there are options other than "zz"). Save file ... does it crash?

If you want to see what the actual formula looks like, you can view it in column J (full path).

OK all... I've narrowed down that the issue has to be with the construct of the LET function, and perhaps some challenge of how the IFNA is being applied. When rewriting the formula like below, the issue disappears. But this formula is written inefficiently, thus my attempt to use the LET function to neaten it up.

=IF(OR($E9="",$E9="zz"),"zz",
IFNA(
IF(VLOOKUP($E9,Working.xlsb!Place_GEOIDTable,33,FALSE)="Unique","zz",
INDEX(Working.xlsb!Place_GNISTable,MATCH($E9,Working.xlsb!Place_GEOID,0),1)),
"zz"))

Are you going to continue this tread?

https://www.excelforum.com/excel-for...ml#post5902681

Originally Posted by TMS

Attachment 854090

I can't give you an answer but I can confirm that the file crashes when I tried to save it.

Thanks. I've developed a workaround that will suffice. I tried talking directly to Microsoft, but they had no answer either. Clearly something going on in the Excel cache or whatnot that the program can't handle. If anyone has another thought, I'm still curious, even though I've moved on. Thanks.