Formulas not updating and not able to save

Metuse

New Member
Joined
Dec 18, 2019
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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).
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Metuse

New Member
Joined
Dec 18, 2019
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,144
Messages
5,622,993
Members
415,944
Latest member
loribear180

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top