Calculating causes formula results to disappear from all sheets

[Gregory123987]

When I save the workbook excel triggers a calculation of the workbook. I have no problem with this and I can always turn it off.

When I go to each sheet and calculate the sheet the results remain visible on each sheet.
When a calculation is done on this workbook, the formula results disappear from each sheet. Specifically, the formulas are still there but the calculated results are not even after the calculation is complete. Is there some reason this happens, and can it be prevented?

Edit: Additional information: 200,000+ formulas on 12 tabs.

 

[Alex Blakenburg]

Under the Forum rules there is not really a way of emailing me the spreadsheet, you would need to put it on Drop box or Onedrive or another sharing platform and then unfortunately make it available to anyone who had the link which you would then post here.

Conditional formatting is volatile and it would be my understanding and doesn't only recalculate when a dependent cell is changed.
They just kill performance and your s/sheet has 4 of them that apply to 500 rows and are all quite complex and that is just looking at 1 of around 14 sheets.

PS: it is quite late here in Australia so I will be login out now.

 

[Gregory123987]

Under the Forum rules there is not really a way of emailing me the spreadsheet, you would need to put it on Drop box or Onedrive or another sharing platform and then unfortunately make it available to anyone who had the link which you would then post here.

Conditional formatting is volatile and it would be my understanding and doesn't only recalculate when a dependent cell is changed.
They just kill performance and your s/sheet has 4 of them that apply to 500 rows and are all quite complex and that is just looking at 1 of around 14 sheets.

PS: it is quite late here in Australia so I will be login out now.

OK - Thanks for trying. I guess I will have to go with calculating the sheets sequentially instead of calculating the workbook.

 

[Alex Blakenburg]

Sorry we can't be of more help. If you could desensitise the workbook and post a link to it, I wouldn't mind taking a look but the nature of your issue would require that the body of the workbook remain pretty much intact since the issue revolves around its size and complexity which would probably make desensitising it too onerous.

 

[Joe4]

Only forwarding client names / DOB / Ethnicity and accumulated totals to the next sheet. I have recommended to the client to convert to a relational database application.

Yep, that is a relational database then.

Hopefully, your client heeds the advice. These type of things typically work a lot better when used in a program designed specifically for that. Of course, that is only true if they design the database well, i.e. follow the typical "Rules of Normalization" when designing the tables (you would NOT have multiple tables or columns for the different months - just one table for those amounts with a column for the month value and one column for the amount - so you would have multiple records for the different months).

 

[Gregory123987]

Yep, that is a relational database then.

Hopefully, your client heeds the advice. These type of things typically work a lot better when used in a program designed specifically for that. Of course, that is only true if they design the database well, i.e. follow the typical "Rules of Normalization" when designing the tables (you would NOT have multiple tables or columns for the different months - just one table for those amounts with a column for the month value and one column for the amount - so you would have multiple records for the different months).

FYI - I am an application developer and I have a full understanding of database design. I just had a question regarding why the results were not re-appearing after full calculation as opposed to sequential worksheet sheet calculations. But thanks for your input.

 

[Joe4]

FYI - I am an application developer and I have a full understanding of database design. I just had a question regarding why the results were not re-appearing after full calculation as opposed to sequential worksheet sheet calculations. But thanks for your input.

I have seen some weird things when data gets too big (that is how I stumbled upon the whole issue of calculation method and maximum formula dependencies tracked).
Perhaps Conditional Formatting calculations have issues too when the number of formula dependencies gets real large.
Even if it does work, it can get so slow and cumbersome that it becomes a nightmare to work with (that is often a sign that you might need another approach).

 

[Alex Blakenburg]

Just having a last look on my iPad. Would it be an option to perform this conditional calculation in a hidden column ?
(scroll to the right it’s pretty long)

=IF(AA19="","",IF(COUNTBLANK($AA19:AA19),IF(COUNT(OFFSET(AA19,,,,MATCH(2,1/($AA19:AA19=""))-COLUMNS($AA19:AA19)))>$J19+COUNT(OFFSET($AA19,,,,MATCH(TRUE,$AA19:AA19="",0))),COUNT(OFFSET(AA19,,,,MATCH(2,1/($AA19:AA19=""))-COLUMNS($AA19:AA19))),""),MOD(IF($J19<10,$J19)+COUNT($AA19:AA19)-1,IF(COLUMNS($AA19:AA19)<10,10,1E+100))+1))

If I am not mistaken it is currently being performed 3 times across 26 columns x 500 rows.
ie AA19:BA518
There is not even a STOP if true turned on (repeated for =10 =5 & =3).
In its own column it could be calculated once then tested for (=10 =5 & =3).
If this is used in multiple sheets the savings could be significant.