Formulas not updating and not able to save

[Metuse]

Hi,
I run into two issues at the moment.

The first issue:
I have two excel files.
-The first is used to enter data by a user. This data is then in a number of hidden sheets used to calculate a few things and finally presented in the last sheet, viewable by the user.
-The second file uses the final data from the first sheet and selects a part of it based on a simply criteria (variable <> 0). This is al dynamic, since the user can change data in the first file.

The first file by itself works fine.
The second file, when initially made, works fine as well: I enter the relevant formulas and these calculate correctly the relevant numbers. However, when updating anything, anywhere, the second file goes blank. The formulas are still there, but nothing happens. I can press enter, ctr+shift+enter, etc all I want, all cells are blank. When I remove all content from the cells and put in the formulas again, it shows again correctly. But this is not desirable, since cells should be updated dynamically without any user action.

Some extra info:
-When in the second file formulas are inserted, they calculate very slowly. They do it correctly, but takes several minutes to complete and I have to click/perform some action, to see that anything happened at all. Before I made the second file, cells in the first file updated just fine.
-The first file is 2.2mb, the second 570kb.
-The first file has 5 sheets, all with about 1500 rows*50 columns with formulas.
-The second file has two sheets, both with about 1500 rows*6 columns formulas.

I suspect it has something to do with instances but I'm not entirely sure. Since the formulas calculate very slowly anyway, I suspect I have done something inefficient.

The second issue:
When I try saving any of the two files, excel freezes. I can do nothing but press escape, only then do I get the error 'calculation is incomplete. recalculate before saving'.
If I then choose 'save without calculating' the file will save.
If I turn on 'update manually' and then press 'calculate sheet' nothing happens (this is a strategy I came across).

 

[coheed1785]

What types of formulas are you using to make calculations?

 

[Metuse]

It works a little better if I only open one file at a time. The first actually works smoothly.

I still experience some issues with the second file though. One column calculates unique values in C column:
=IFERROR(INDEX($C$2:$C$2000,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$2000),0)),"")
This one calculates horribly slow. When changes are made in the other file (rows added, deleted, values changed), this sheet updates quite slow, but especially this formula takes a LONG time to process these changes.
Saving however now works (at least after I've visually checked all calculations are completed).

These formulas are in A, B, C, D columns:
- A finds all value (names, around 33) in a column in file. Names are always duplicates (for every week in a year is a name), however, some are left out here based on two criteria (if a value = 0 or if another value is empty) --> =IF(AND('path/[file.xlsm]FTE'!M2<>0,'path/[file.xlsm]FTE'!H2<>""),'path/[file.xlsm]FTE'!H2,"")
-B finds all week numbers (=52 weeks) --> =IF(A2<>"",'path/[file.xlsm]FTE'!F2,"")
-C concatenates name+week (=33*52 rows), so the result is something like C2=Little John|2020-01 --> =IF([@Naam]<>"",[@Naam]&"|"&[@Week],"")
-D finds a value (33*52 values) associated with that unique name-week combination --> =IF(A2<>"",'path/[file.xlsm]FTE'!M2,"")
-E then finally find all unique value in C, thus leaving out all blank lines (this is the actual objective of this particular sheet, to leave out all lines that correspond to the criteria in A).

E needs to calculate unique values in 1700+ rows, of which about 1500 are unique, since only a few blank rows are left out. I guess that's just a lot to calculate which causes to slow updating of values. Is there a way to make this more smoothly?